#SQLPASS Day 1 — #BreathOfSQL [aka SQLOS]

This was the first day of SQLPASS and I have decided to take a Indiana Jones theme to my posts.  My first post will be entitled Breath Of SQL.

I was fortunate enough to attend Maciej Pilecki’s [Twitter][BLOG] class on Secrets of the SQLOS.  Let me start out by saying that SQLOS is perhaps the least understood and the most fascinating part of SQL Server.  Building an O/S inside a database engine to perform the complex tasks it has to complete?  What isn’t cool about that?  The best part was that Maciej was teaching the class and he is an absolute fantastic presenter.

The class was divided into 5 parts:
1.  Introduction/Overview of SQLOS Architecture
2.  Process/Thread Scheduling & Execution
3.  SQL Server Memory Management
4.  Advanced Management & Troubleshooting
5.  Summary/Q&A

It did not help that I did not fall asleep until very late the night before so I was on fumes by the afternoon.  However the information which was shared across, especially regardingscheduling, execution and memory management kept me wide awake.  We had a very engaged audience with lots of people, including myself, asking thoughtful and relevant questions.

In my opinion they Maciej has hit a home run with this class.  He could do a 3 or 5 day class on the SQLOS and still have more to cover.

Tomorrow will be Allan Hirt’s [Twitter] [BLOG] AlwaysOn class or as I call it Word Of SQL class.  Shaping up to be a good week.

Advertisements

#SQLPASS Blogging

My good friend Jimmy May [Twitter] [BLOG] has been hassling me about blogging.  As I explain to him each time we talk, via Twitter mind you, that I am far too busy with my job, finding ways to kill everyone useless DEV we have at my company  and dealing with #NDA badness with MS to write down anything.  Now that SQLPASS has come again I will BLOG daily just to make him happy.  For some odd reason he wants me to speak in community.  Like anyone wants to listen to a wanna-be janitor DBA like me.

NOTE:  Jimmy I checked your site and the last time you wrote something was 11/24/2009.  Come on buddy get with it. 😉

Man Crush (or Paul Randal is AWESOME)

Normally I don’t promote my man crush’s publicly, except for Jimmy May (twitter) or Buck Woody (twitter), but today I had to say I fell in  LOVE with Paul Randal (twitter).  So here is the story:

 

At ~10:00 Pacific today, while I was BS’ing with 2 of the DBA’s at my company from how much the DBE’s irritate us to what is wrong with our storage infrastructure, the pager went off [CLOISTER BELL].  The page indicated one of our DS/DW virtual SQL 2008 instance was offline.  I ran over and got onto the Windows 2008 failover cluster tool and viewed the status of the cluster.  I saw that SQL Server application was offline.  Then I noticed the transaction log volume was offline/RED/non-existant.  We immediately contacted our SOC and had them page out the Storage, Windows and Service Management teams.

While I waited for a bridge to be created I tried to bring the transaction log volume online.  That didn’t work.  It looked like the storage was not there.  Now it is 10AM and no one should be doing anything at 10AM, RIGHT?!?!?!  The bridge was started, a priority 2 incident was created and storage got on the call.  The person in question, whose name will be kept secret because I might start cursing, identified he was cleaning up retired  SVSP storage which we removed yesterday he accidentally removed the wrong volume.  This got my blood boiling and so I asked “When is it coming back?”  This person said it was GONE gone, as it removed/data destroyed.  Holy Crap!  So he as in the process of creating a new logs volume.

Now thankfully the master/model/msdb log files were not on this volume when SQL 2008 was installed.  Also the logs volume was a dependency of SQL so when it was gone, SQL crashed and alerted us.  We could bring up SQL just fine but all the user databases were in SUSPECT mode.  The funny thing is that when you go through SSMS it didn’t actually say the word SUSPECT but rather showed the name of the database with no other icons next to it.  What to do next.  Well there is your check list:

  1. When was your last FULL & DIFF backups?
    For 3 of the 4 core databases we had a recent FULL/DIFF.  For the database which was ~6TB in size we had NONE.
  2. Why didn’t you have a backup?
  3. WHERE ARE YOUR BACKUPS?

Now our team huddled together and we started to come up with ideas.  My manager, who was on IM mind you, remembered in the SQL 2000 days you could use sp_attach_single_file_db to attach a database without a transaction log file.  Since we had a DBA tools database that was down because of the missing t-log file, we waited until the new log volume was created, built out the folder structure needed for recovery and tried:

  1. sp_attach_single_file_db — Failed.
  2. CREATE DATABASE … ON FILE … FOR ATTACH_REBUILD_LOG — Failed.
  3. DBCC CHECKDB (<DB>,REPAIR_ALLOW_DATA_LOSS) — Failed.

Now what can we do?  So I did a Google search [no I will never BING] and I found the answer.  Paul Randal had blogged in August 28, 2008 on how to fix this exact issue.  We had forgotten to put the database into EMERGENCY and SINGLE_USER mode prior to running the DBCC command.  So what we had to run was:

ALTER DATABASE <DB> SET EMERGENCY;
GO

ALTER DATABASE <DB> SET SINGLE_USER;
GO

DBCC CHECKDB (<DB>, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

We took the smallest of the 4 core user databases and ran the steps above.  In about 38 minutes the DBCC CHECKDB finished for a 385GB database.  We did a check and everything look okay [tables, view, data, etc].  Given the size/time for this test we estimated the recovery of the next 2 core databases [1.9 & 3.6 TB each] would take about 2.5 and 5 hours respectively.  The largest database, which is ~6TB in size, will take about 9 hours to get done.  The speed was estimated at 10GB per minute, taking into account our storage infrastructure, memory and MAXDOP setting of the server.  The recovery is on its way and given we will have some data loss we really lucked out.  By noon tomorrow we should be back up 100%.

 

The moral of this story kids?  Paul Randal is a rockstar and I will buy him a round of anything he wants the next time I see him.  As for our storage group, that is another sad story.

New Virtual SQL/SSAS Install on a Windows Server 2008 Cluster

These are the directions my company uses to install virtual SQL/SSAS instances on a Windows 2008 Cluster.  Hope you enjoy it.

NOTE:  Please refer to the steps outlined on the http://www.mssqltips.com/tip.asp?tip=1687 for virtual SQL clustered installs.

Pre-Requisites:

To install a new SQL/SSAS instance on a Windows Server 2008 cluster, the following must be present before the install can proceed.

  • Cluster Network Name – Requested by the WSE team, the hostname of the cluster.
    Example: HELWD1CL01P 

  • Cluster Network Object – Same as the cluster hostname but its entry in Active directory.
    Examples:  HELWD1D1CL01P$ 

  • Cluster Node(s)– Requested by the WSE team, the hostname of a nodes in the cluster.
    NOTE:  We need only 1 node active in a cluster to perform SQL installs.
    Example:  HELWD1DBCL01P 

  • SQL Service Account – Requested by the DBA team, this SQL service account will own/start all SQL/SSAS instances on a given cluster.  This login must also be added to the SQL Servers NT security group.
    NOTE:  This account needs trusted for deletion set [Active Directory User Property à Delegation à Trust this user for delegation to any service (Kerberos only)].
    Example:  INSPINC\HELTESTSQLSVC 

  • Cluster MSDTC Hostname – Requested by the DBA team, this is the DNS entry for the clustered Microsoft Distributed Transaction Coordinator (MSDTC).
    NOTE:  Each cluster needs only 1 MSDTC host.
    Example:  HELWD1MSDTC01P 

  • Cluster MSDTC VCO – Requested by the DBA team, this is the Active Directory Virtual Computer Object [VCO] for the cluster MSDTC hostname.
    NOTE:  This is the same as the cluster MSDTC hostname.
    Example:  HELWD1MSDTC01P 

  • Cluster MSDTC Storage – Requested by the DBA team, this storage is required for installing a clustered MSDTC.
Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
Z:\ FC 5 1
  • Virtual SQL/SSAS Instance Hostname – Requested by the DBA team, this is the DNS entry for the virtual SQL/SSAS instances.
    NOTE:  The virtual SQL instance hostnames must be in the same IP subnet as the cluster network name/cluster node(s).
    SQL Instance Example:  DWWMAINVSQL5P
    SSAS Instance Example:  DWWVOLAP5P 

  • Virtual SQL/SSAS Instance VCO Requested by the DBA team, this is the Active Directory Virtual Computer Object [VCO] for the virtual SQL/SSAS instance hostname.
    NOTE:  This is the same as the Virtual SQL/SSAS instance hostname.
    Example:  DWWMAINVSQL5P
  • Virtual SQL/SSAS Instance Storage – Requested by the DBA team, this storage is required for installing a virtual SQL/SSAS instance.  All root drive letters are available except A-F, W [Witness] and Z [MSDTC].
    NOTE:  By default we request a root drive letter, 2 data, 1 log and 1 tempdb volume for all SQL instances.  For SSAS instance a root drive letter, 1 data and 1 tempdb volume is required.  Always start with root drive letter G.
    SQL Storage Example:
Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
G:\ FC 5 1
G:\Data1 FC 5 50
G:\Data2 FC 5 50
G:\Logs FC 1 25
G:\TempDB FC 1 25

SSAS Storage Example:

Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
G:\ FC 5 1
G:\Data1 FC 5 50
G:\TempDB FC 1 30

Install of a new virtual SQL/SSAS Instance:

Here are the steps to follow in order to install a new virtual SQL/SSAS instance on a given cluster.

  1. Login to each node of the cluster with the NT credentials.  Verify you can login with any errors.
  2. Verify and add, if needed, the following users and groups to the local administration group on each node of the cluster.
    SQL Service Account
    DBA Security Group
    DBA Service Account
  3. Run the Local Group Policy Editor [gpedit.msc] and verify/add the SQL Service Account to the following policy, if needed:
    Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment
    Lock pages in memory
    Perform volume maintenance tasks 

  4. Run the Registry Editor [regedit] and create, if necessary, the following registry key.
    HKLM –> SYSTEM –> CurrrentControlSet –> Control –> Lsa
    DisableLoopbackCheck [DWORD(32) Value] –> 1 [Value]
  5. Run the Failover Cluster Manager [cluadmin.msc] and install/configure, if needed, the MSDTC clustered instance.
    NOTE:  Refer to instructions on the http://www.mssqltips.com/tip.asp?tip=1709 for detailed steps.
  6. Run the Component Services [comexp.msc] to set cluster DTC options, if needed.

NOTE:  This need to only happen once on the cluster.
Console Root –> Component Services –> My Computer –> DTC –> Clustered DTCs –> <MSDTC Hostname> –> Properties –> Security
{Checkbox Settings}
Network DTC Access
Allow Inbound
Allow Outbound
Enable XA Transactions

  1. Create the following directories under the root drive letter for each virtual SQL/SSAS instance you will be installing.
    NOTE:  You will need to know the instance name for each virtual SQL instance.
    SQL Example:
    <ROOT DRIVE LETTER>:\Data1\MSSQL\Data
    <ROOT DRIVE LETTER>:\Data2\MSSQL\Backup
    <ROOT DRIVE LETTER>:\Data2\MSSQL\Data
    <ROOT DRIVE LETTER>:\Logs\MSSQL\Log
    <ROOT DRIVE LETTER>:\TempDB\MSSSQL\TempDB 

    SSAS Example:
    <ROOT DRIVE LETTER>:\Data1\MSAS\Data
    <ROOT DRIVE LETTER>:\Data1\MSAS\Backup
    <ROOT DRIVE LETTER>:\Data1\MSAS\Log
    <ROOT DRIVE LETTER>:\TempDB\MSAS\TempDB

  2. Create a dummy filename under the root drive letter to identify the virtual SQL instance which will be installed:
    <ROOT DRIVE LETTER>:\<VIRTUAL SQL/SSAS HOSTNAME>$<INSTANCE NAME>_DO_NOT_REMOVE.TXT
  3. Using nslookup, record the IP address for the virtual SQL instance you will be installing.

10.  Run the SQL installation program for the virtual SQL instance you will be installing.
NOTE:  Refer to instructions on the http://www.mssqltips.com/tip.asp?tip=1709 for detailed steps.  Accept default values except for the steps identified below.

Step 7 –
Instance Features [SQL]:
Database Engine Services
SQL Server Replication
Full-Text Search

Instance Features [SSAS]:
Analysis Services

Shared Features:
Business Intelligent Development Studio
Client Tools Connectivity
Integration Services
Client Tools Backwards Compatibility
SQL Server Books Online
Management Tools – Basic & Complete

Step 8
Specify the correct SQL/SSAS Network Name/Named Instance:
Example:
SQL Network Name = DWWMAINVSQL5P Instance = DWMAIN5
SSAS Network Name = DWWVOLAP5P Instance = DWOLAP5

Step 10
Specify the following cluster resource group name:
Example:
SQL      SQL Server (<DWWMAINVSQL5P>)
SSAS   SQL Analysis Services (<DWWVOLAP5P>)

Step 11
UNCHECK any drives specified and check the root drive letter for the SQL/SSAS instance in question.

Step 12
UNCHECK DHCP and specify the IP address of the appropriate SQL/SSAS Network Name.

Step 14 –
Service Accounts:
Click “Use the same account for all SQL Server services” and specify the SQL Service Account login and the password.  The passwords is located in the Party List.

Collation:
Specify the collation Latin1_General_CI_AS for Data Warehouse SQL Instances only.  Use default for all other installs.

Step 15 –
Add the following logins for SQL/SSAS Server administrators.
SQL Service Account
DBA Security Group
DBA Service Account

SQL Server Installs
Click Mixed Mode and specify an ‘sa’ password.

Specify the following values for directories:
Data root           <ROOT DRIVE LETTER>:\Data1\MSSQL
User dir:            <ROOT DRIVE LETTER>:\Data1\MSSQL\Data
User log:           <ROOT DRIVE LETTER>:\Logs\MSSQL\Log
TempDB dir:      <ROOT DRIVE LETTER>:\TempDB\MSSQL\TempDB
TempDB log:     <ROOT DRIVE LETTER>:\TempDB\MSSQL\TempDB
Backup:            <ROOT DRIVE LETTER>:\Data2\MSSQL\Backup

Analysis Services Installs
Specify the following values for directories:
Data root           <ROOT DRIVE LETTER>:\Data1\MSAS\Data
User dir:            <ROOT DRIVE LETTER>:\Data1\MSAS\Log
TempDB dir:      <ROOT DRIVE LETTER>:\TempDB\MSAS\TempDB
Backup:            <ROOT DRIVE LETTER>:\Data1\MSAS\Backup

11.  Repeat step above for as many SQL/SSAS instance as needed.  Once your installs are complete, upgrade to the latest SQL CU hotfix.

Deploying User Defined Performance Counters

Performance Collector Application Box

It is recommended that all user defined performance counter collections for SQL instances [physical or virtual] occur from dedicated application box(s).  Based on your security policy [data center or production vs. test/demo], there maybe 1 or more of these application box(s) setup for this purpose.  The performance collector SQL database, which would store the user defined performance counters, can also be segregated based on security policy.

The dedicated application box(s) will need firewall access opened to the SQL instances they are collecting from.  The firewall rules required for remote performance collection are the same as those required for SMB access.  The same application will need to have firewall access opened to log the data to the performance collector SQL instance.  You may need to disable any virus checking application as it may interfere with remote performance collection.

Here is an example of rules that you may need to set  for access.  Please consult with the network/security team for rules and questions.

Performance Collector Application Box à Monitored SQL Instances

Source Host:                 DBAWPRPL1P ß Sample hostname of the application collection box(s)

Destination Host:           <IP Network Subnet>, <IP Network Subnet>, …, <IP Network Subnet>

Ports:                           TCP – 135-139,445

                                    UDP – 135-139, 445

Performance Collector Application Box à Performance Collector SQL Instances

Source Host:                 DBAWPRPL1P ß Sample hostname of the application collection box(s)

Destination Host:           DBAMONVSQL1P ß Sample performance collector SQL instance

Ports:                           TCP – 1433

                                    UDP – 1434

Setting up System DSN for Performance Collector SQL Instance

Now you must configure a System DSN [System Data Source] so the performance collectors can write to the backend performance collector SQL instance.

  1. Go to Start –> Control Panel –> Administrative Tools.
  2. Open Data Sources (ODBC) tool.
  3. Go to System DSN –> Add.
  4. Choose SQL Server Native Client and click Finish.
  5. Specify the name DSN name, description [if needed] and name of the SQL instance [hostname or hostname\instance].  Click Next.
  6. Choose either an integrated Windows authentication or SQL login.  Click Next.
  7. Specify the default database where the performance collector tables exist.  Click Next.
  8. Specify any additional options you wish, as needed.  Click Finish when complete.

 

Manually Creating User Defined Performance Counters

You now need to create a user defined performance counter collection.  This is where you specify all the counters you wish to collect from the monitored SQL instance.  This procedure assumes you are using Windows 2008 Server for your application collector box.

  1. Go to Start –> Run and type in perfmon.
  2. Open Data Collector Sets –> User Defined à right click New –> Data Collector Set.
  3. Specify a collector name.  It is recommended that you come up with a standard for all monitored SQL instance collector names.  For example:  SMPWPROVVSQL1P_Benchmark_DSN, where the _Benchmark_DSN is also the suffix used for a monitored SQL instance.
  4. Choose Create manually (Advanced) and click Next.
  5. Choose Create data logs –> Performance counter and click Next.
  6. Click Add and specify the name of the remote computer you wish to collect from.  You need to specify \\<HOSTNAME> to a remote SQL instance.  For example:  \\SMPWPROVVSQL1P.  Click Browse.
  7. Once connected, scroll the list of available counters and specify those you wish to collect and click Add.  After you have chosen all counters you wish to collect, click OK.
  8. Change the Sample interval, if you wish, at this time.  Click Finish to complete.
  9. Right click on the newly created user defined counter name and choose Properties.  Change the Run As from SYSTEM to a secure service account.  This service account should have BULK ADMIN and write permission on the performance collector SQL database.  Click OK.

10.  Click on the newly created user defined counter name and double click on DataCollector01.  Under Log format choose SQL.  Under Data Source Name choose the System DSN which was created above.  You may also specify the sample internal time, if needed.

11.  Click Add to add any additional performance counters.  Follow steps above.  Click OK when complete.

12.  Right click on the data collector name and choose Start.  After a few moments you should see the icon for the counter change to a running state.

13.  Login to the performance collector SQL database and verify data is being logged from the SQL instance being monitored.

14.  Repeat as needed.

User Defined Performance Counter Using a Template File

You may also create a user defined performance counter collection from a template file.  This allows you to simply modify a template instead of re-entering all performance counters by hand.  This procedure assumes you are using Windows 2008 Server for your application collector box.

  1. Go to Start –> Run and type in perfmon.
  2. Open Data Collector Sets –> User Defined.  Right click on a user defined counter you wish copy and choose Save Template.  Specify the template name and location of the file.
  3. Edit the XML template file and change values such as the name of the SQL instance you are monitoring, directory folders, databases to monitor, etc.  Save the file when complete.
  4. Open Data Collector Sets –> User Defined –> right click New –> Data Collector Set.
  5. Specify a collector name.  It is recommended that you come up with a standard for all monitored SQL instance collector names.  For example:  SMPWPRSVSQL1P_Benchmark_DSN, where the _Benchmark_DSN is also the suffix used for a monitored SQL instance.
  6. Choose Create from a template (Recommended) and click Next.
  7. Choose Basic and click Browse.  Choose the XML template file you have modified.  Click Next 2 times.
  8. Change the Run As from SYSTEM to a secure service account.  This service account should have BULK ADMIN and write permission on the performance collector SQL database.  Choose Open properties for this data collector set and click Finish.  Verify everything then click OK.
  9. Click on the newly created user defined counter name and double click on DataCollector01.  Under Log format verify it is set to SQL and the correct Data source name is set.  Verify the sample internal time, if needed.

10.  Click Add to add any additional performance counters.  Follow steps above.  Click OK when complete.  You may need to provide credentials for the secure service account again, if needed.

11.  Right click on the data collector name and choose Start.  After a few moments you should see the icon for the counter change to a running state.

12.  Login to the performance collector SQL database and verify data is being logged from the SQL instance being monitored.

13.  Repeat as needed.

Configuring Performance Collector SQL Backend DB/Tables

 To collect performance counter data from SQL instances, you need to choose a SQL instance which will house the performance collector database and tables.  It is recommended that you use a centralized performance collector database server for a given data center location and/or network zone [production versus test/demo].  The database server should have some higher availability [e.g. clustered with failover nodes].

Table Layout

The performance collector SQL backend database consist of 3 core tables.

  • CounterData – This table contains the raw data collected by each of the performance counters for a given SQL instance monitored.  Based on the collection frequency, there will be 1 row for each performance counter collected for the collection.  This table will go in infinitely and needs to be pruned/maintained.

CREATE TABLE [dbo].[CounterData](

       [GUID] [uniqueidentifier] NOT NULL,

       [CounterID] [int] NOT NULL,

       [RecordIndex] [int] NOT NULL,

       [CounterDateTime] [char](24) NOT NULL,

       [CounterValue] [float] NOT NULL,

       [FirstValueA] [int] NULL,

       [FirstValueB] [int] NULL,

       [SecondValueA] [int] NULL,

       [SecondValueB] [int] NULL,

       [MultiCount] [int] NULL,

CONSTRAINT [PK_CounterData] PRIMARY KEY CLUSTERED

([GUID] ASC, [CounterID] ASC, [RecordIndex] ASC))

GO

  • CounterDetails – This table contains a list of performance counters for a given SQL instance monitored.  This table has finite growth based on the number of SQL instances monitored and the performance counters captured.

CREATE TABLE [dbo].[CounterDetails](

       [CounterID] [int] IDENTITY(1,1) NOT NULL,

       [MachineName] [varchar](1024) NOT NULL,

       [ObjectName] [varchar](1024) NOT NULL,

       [CounterName] [varchar](1024) NOT NULL,

       [CounterType] [int] NOT NULL,

       [DefaultScale] [int] NOT NULL,

       [InstanceName] [varchar](1024) NULL,

       [InstanceIndex] [int] NULL,

       [ParentName] [varchar](1024) NULL,

       [ParentObjectID] [int] NULL,

       [TimeBaseA] [int] NULL,

       [TimeBaseB] [int] NULL,

CONSTRAINT [PK_CounterDetails] PRIMARY KEY CLUSTERED

([CounterID] ASC))

GO

  • DisplayToID – Contains information, such as rows captured, for a given SQL instance monitored.

CREATE TABLE [dbo].[DisplayToID](

       [GUID] [uniqueidentifier] NOT NULL,

       [RunID] [int] NULL,

       [DisplayString] [varchar](1024) NOT NULL,

       [LogStartTime] [char](24) NULL,

       [LogStopTime] [char](24) NULL,

       [NumberOfRecords] [int] NULL,

       [MinutesToUTC] [int] NULL,

       [TimeZoneName] [char](32) NULL,

CONSTRAINT [PK_DispalyToID] PRIMARY KEY CLUSTERED

([GUID] ASC),

CONSTRAINT [UQ_DiksplayToID_DisplayString] UNIQUE NONCLUSTERED

([DisplayString] ASC))

GO

Possible Enhancements — Partitioning & Compression

Since the CounterData table contains the raw data we would wish to query and act upon, the design of the table leaves a lot to be desired.  The clustered index is on a composite primary key column [GUID,CounterID,RecordIndex] and there are not other indexes on the table by default.  Also the CounterDateTime column, which stores the time the collection took place, is stored as a CHAR(24) which makes queries very difficult since you need to convert the CHAR(24) to a DATETIME in order to perform range based queries.  There is also the fact that there is no partitioning [for maintenance of the table] or compression [to keep the database size realistic].  There is also the fact that data is inserted into the CounterData table using BULK INSERT, which makes it hard to change the core table since you can’t add/modify any columns definitions.

Given the BULK INSERT attempts to insert data into the CounterData table, there is nothing to say that CounterData can’t be a view instead.  The solution below presumes you are performance collector database is housed on a SQL 2008 Enterprise database instance.

  • Create a new DAYOFYEAR [1-366] partition scheme and function for maintenance of the table.

CREATE PARTITION FUNCTION [PF_PartitionDay](SMALLINT) AS RANGE FOR VALUES

(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79, 80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119, 120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139, 140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159, 160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179, 180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199, 200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219, 220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239, 240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259, 260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279, 280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299, 300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319, 320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339, 340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359, 360,361,362,363,364,365,366)

GO

CREATE PARTITION SCHEME [PS_PartitionDay] AS PARTITION [PF_PartitionDay] ALL TO ([PRIMARY])

GO

  • Drop the current CounterData table, create a new CounterData_TABLE table and build CounterData as a view.

DROP TABLE [dbo].[CounterData]

GO

CREATE TABLE [dbo].[CounterData_TABLE](

       [GUID] [uniqueidentifier] NOT NULL,

       [CounterID] [int] NOT NULL,

       [RecordIndex] [int] NOT NULL,

       [CounterDateTime] [char](24) NOT NULL,

       [CounterValue] [float] NOT NULL,

       [FirstValueA] [int] NULL,

       [FirstValueB] [int] NULL,

       [SecondValueA] [int] NULL,

       [SecondValueB] [int] NULL,

       [MultiCount] [int] NULL,

       [EventDate] [datetime2](7) NOT NULL,

       [DayOfYear] [smallint] NOT NULL,

 CONSTRAINT [PK_CounterData_TABLE] PRIMARY KEY NONCLUSTERED

([GUID] ASC,[CounterID] ASC,[RecordIndex] ASC,[DayOfYear] ASC))

ON [PS_PartitionDay](DayOfYear)

GO

/* Add default values for new EventDate & DayOfYear columns. */

ALTER TABLE [dbo].[CounterData_TABLE] ADD  CONSTRAINT [DF_CounterData_TABLE_EventDate]  DEFAULT (GETDATE()) FOR [EventDate]

GO

ALTER TABLE [dbo].[CounterData_TABLE] ADD  CONSTRAINT [DF_CounterData_TABLE_DayOfYear]  DEFAULT (DATEPART(DAYOFYEAR,GETDATE())) FOR [DayOfYear]

GO

/* New clustered index on EventDate column */

CREATE CLUSTERED INDEX [IDX_CounterData_TABLE_EventDate] ON [dbo].[CounterData_TABLE] ([EventDate] ASC,[DayOfYear] ASC)

ON [PS_PartitionDay](DayOfYear)

GO

/* New clustered index on CounterID column */

CREATE NONCLUSTERED INDEX [IDX_CounterData_TABLE_CounterID] ON [dbo].[CounterData_TABLE] ([CounterID] ASC)

INCLUDE ([EventDate],[DayOfYear])

ON [PS_PartitionDay](DayOfYear)

GO

/* Create new CounterData VIEW. */

CREATE VIEW [dbo].[CounterData] AS

SELECT [GUID]

      ,[CounterID]

      ,[RecordIndex]

      ,[CounterDateTime]

      ,[CounterValue]

      ,[FirstValueA]

      ,[FirstValueB]

      ,[SecondValueA]

      ,[SecondValueB]

      ,[MultiCount]

FROM  [dbo].[CounterData_TABLE]

GO

  • Modify the performance collector tables for ROW compression.

ALTER INDEX ALL ON CounterData_TABLE REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

ALTER INDEX ALL ON CounterDetails REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

ALTER INDEX ALL ON DisplayToID REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

SQL Server Performance Monitoring Through PerfMon

This is a list of Windows performance counters that my company captures to monitor, in real-time, our SQL infrastructure.  The performance counters are captured every 5 [OLTP] or 15 [non-OLTP] seconds, written to a SQL database table(s) in real-time and later viewed through automated reports [SSRS].  We create this method to monitor our SQL infrastructure because: 1)  We didn’t want to spend a lot of $$; 2) This tools comes with Windows and is available for free; and 3) No tool out there gave us the granularity of <1 minute capture and analysis.  We also setup a job to aggregate data older than 14 days to another database for historical analysis.

We capture these performance counters from a trusted Windows box which has the necessary firewall rights the SQL instances [virtual or physical].  I will go into detail, at a later time, on how to create the SQL database table(s) for capturing the data and configuring performance counters to write to the tables automatically.

LogicalDisk (Root Drive Letter or Mount Point {R:\Data1})

  • Avg. Disk Queue Length — Average number of I/O requests [read + write] waiting for disk access.
  • Avg. Disk sec/Read — Average time, in seconds, of a read of data from disk.  Measurement of read latency.
  • Avg. Disk sec/Transfer — Average time, in seconds, of a trasnfers onto the disk.  Measurement of read & write latency.
  • Avg. Disk sec/Write — Average time, in seconds, of a write of data to disk.  Measurement of write latency.
  • Disk Reads/sec — Number of reads from the disk, per second.
  • Disk Transfers/sec — Number of transfers [reads & writes] to disk, per second.
  • Disk Writes/sec — Number of writes to the disk, per second.

Memory

  • Available MBytes — Available physical memory, in MBytes, available for a given process or system use.
  • Pages/sec — Rate at which pages are read from or written to disk to resolve hard page faults.

SQLServer *or* MSSQL$<INSTANCE>

Access Method

  • Page Splits/sec — Number of page splits per second that occur as a result of overflowing index pages.

Buffer Manager

  • Buffer cache hit ratio — Percentage of pages that were found in the buffer pool without having to perform a read from disk.
  • Page life expectancy — Number of seconds a page will stay in the buffer pool without references.

Database Mirroring(_Total)

  • Log Send Queue KB — Total number of KB of log that have not been sent to the mirror server.
  • Redo Queue KB — Total number of KB that redo on the mirror database is behind the hardened log.
  • Send/Receive Ack Time — Milliseconds messages waited for acknowledgement from the partner per second.

Database(_Total) & Database(<DB_NAME>)

  • Data File(s) Size KB — Cumulative size of all the data files in the database.
  • Transactions/sec — Number of transactions started for the database.
  • Write Transactions/sec — Number of transactions which wrote to the database in the last second. <– SQL 2008 ONLY!

General Statistics

  • User Connections — Number of users connected to the SQL instance.

Latches

  • Latch Waits/sec — Number of latch requests that could not be granted immediately and had to wait before being granted.

Locks(_Total)

  • Lock Waits/sec — Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.

Memory Manager

  • Memory Grants Pending — Current number of processes waiting for a workspace memory grant.

SQL Statistics

  • Batch Requests/sec— Number of batch requests received by the SQL instance, per second.
  • SQL Re-Compilation/sec — Number of SQL recompiles that occurred, per second.

SQL 2008 ONLY — Workload Group Stats(default)

  • Active requests — Number of active requests in the default workgroup.
  • Blocked tasks — Number of blocked tasks in the default workgroup.
  • CPU usage % — A method through which you can measure the CPU consumed by this SQL instance.  Helpful when stacking instances on 1 box.

Network Interface(<NIC Name>)

  • Bytes Total/sec — Rate at which bytes are sent and received over each network adapter, including framing characters.
  • Current Bandwidth — Estimate of the current bandwidth of the network interface in bits per second (BPS).

Process(sqlserver)

  • % Processor Time — Percentage of elapsed time that all of process threads used the processor to execution instructions.  The value is cumulative over all logical processors.
  • % User Time — Percentage of elapsed time that all of process threads spent executing code in user mode.  The value is cumulative over all logical processors.
  • Working Set — Set of memory pages touched recently by the threads in the process.

Processor(%Total)

  • % Processor Time — Percentage of elapsed time that the processor spends to execute a non-Idle thread.  A measurement of overall CPU pressure [up to 100%].

System

  • Context Switches/sec — Combined rate at which all processors on the computer are switched from one thread to another.
  • Processor Queue Length — Number of threads in the processor queue.  Need to divide by the number of logical processors to get actual value.

NOTE:  The firewall ports that you need opened to capture data from the trusted host are the same as those for SMB [TCP & UDP ports 135-139, 445].