Category Archives: Performance Tuning

How many SQL Instance can I host efficiently on existing server?

It’s a very open ended questions and before answering this question you need to understand what kind of application you are using and how much transaction they makes on database. You need to understand size of databases and how much CPU\MEM is required to run those databases on SQL server also it more depends on your performance capacity of Physical Server\VM, I mean if you are having a server with multiple cores 100 Gigs of mem and super-fast SAN you can host multiple instances.

This kind of consolidation of instance has its own Pros and Cons as this could be a beneficial but it can also be a headache for a DBA or Architecture team.

Why Would You Use Multiple SQL Server Instances?

  • You can save lot of cost for hardware
  • License costs for SQL Server currently are by CPU socket by machine, not by instance on a machine. Doubling up the instances on a machine can save lot of cost
  • Fully utilize the hardware performance to its limit

Performance Bench-marking is very important for consolidation (running multiple instances on server), this bench-marking will provide you information like how much MIN\MAX CPU and how much MIN\MAX memory is required to run this SQL Instance, also how much storage capacity is required. Apart from these things network bandwidth and I/O plays an important role here before making decision to host multiple instances on one Server.

What Important counters I must collect for defining my benchmark of SQL Instance?

Identify the Physical configuration of your server on which you are hosting this SQL Instance


You need to focus on various trends for below performance counters to define minimum, average and maximum value required for your SQL Instance to run.

  1. Identify CPU utilization trend over a period of time to identify CPU required to run SQL Instance: This will let you decide how much computing power\processors are required to host this SQL instance.


2. Identify CPU utilization of database: You can easily get this information from T-SQL what is the average utilization of CPU for your database, a job can be configured on a server and you can draw a trend and actually see which database is consuming most CPU

 WITH db_cpu_stats 
 AS (SELECT databaseid, 
 db_name(databaseid) AS [database name], 
 sum(total_worker_time) AS [cpu_time_ms] 
 FROM sys.dm_exec_query_stats AS qs 
 CROSS apply (SELECT CONVERT(int, value) AS [databaseid] 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle) 
 WHERE attribute = N'dbid') AS f_db 
 GROUP BY databaseid) 
 [database name] AS 'Name', 
 [cpu_time_ms] AS 'CPU Time [MS]', 
 cast([cpu_time_ms] * 1.0 / sum([cpu_time_ms]) 
 OVER() * 100.0 AS decimal(5, 2)) AS 'CPU [%]' 
 FROM db_cpu_stats 
 WHERE databaseid <> 32767 -- ResourceDB 
 ORDER BY cast([cpu_time_ms] * 1.0 / sum([cpu_time_ms]) 
 OVER() * 100.0 AS decimal(5, 2)) desc 
 OPTION (recompile)



3. Identify Memory utilization of database

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';
;WITH src AS
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

DBmem4. Identify below counters usage to define MIN\MAX memory and other memory configurations.

These counters will help to understand about the load on the SQL Server. Monitor these counters over a period of time and analyze the trend that will actually give you min max and average utilization of these parameters




5. Identify the database usage trends to define capacity management


Apart from all this you need to focus on the of I/O sub system, Your I/O subsystem must be fast and further separation your Logs, Data and TEMPDB data files, will give you an additional advantage

Once you define you bench-marking on the basis of above counters which will help you in defining cpu, memory, storage and bandwidth requirement for a SQL Instance.


How to handle Memory Pages Per Second is too High?

Some time when you configure SCOM (System Center Operation Manager – Monitoring Tool)  in your environment and configure a SQL Management Pack to monitor “Memory Pages Per Second” then you will get the below alert as mentioned ” Memory Pages Per Second is too High. – The threshold for the Memory\Pages\sec performance counter has been exceeded”

Let’s first discuss how this alert is raised by per System Center Operation Manager

Here this counter Pages/sec means it’s the rate at which pages are read from or written to disk to resolve hard page faults. In more detail it identifies the rate of pages read from and written to disk for resolving memory requests for pages that are not in memory. In other words, Pages/Sec represents pages input/sec and pages output/sec. (Pages output represents the pages removed from memory heading to disk.).  If you talk about page fault it happens when the memory manager does not find the block of memory that it needs in the cache. That memory has been swapped out to disk, and this will degrade your Server \SQL server performance.

This rule will generate an alert when the pages/sec exceeds the defined threshold for a sustained period of time and Default is Memory\Pages/Sec >50).

When this performance threshold has been exceeded it often means that the system is low on physical memory because the operating system is reading from the hard disk to recover information from the virtual memory. When the system is in this state the overall system performance is significantly diminished and result in poor operating system and application performance.

Hence please make sure that this rule must be tuned accordingly to the server configuration keeping in mind about the performance capacity of server before it can be used. So before enabling this rule create a baseline Pages/Sec performance signature for each system. Once you create a baseline, set a default threshold that will work for the largest percentage of managed nodes and then use the override feature to define threshold for the exception cases.

From the SQL Server perspective when you receive this kind of alert it’s a primary indicator suggesting that there could be an issue of memory pressure\bottleneck, but that depends on several factors how frequent you are getting this alert from same server and how it is configured, allocated memory to SQL, no. of instances running on server etc.

Note: A high number in the Pages/Sec counter does not necessarily mean that you have a memory problem, it could be a temporary issue but if the same server is generating same kind of alert then please monitor the below mentioned counter over a period of time to confirm if there is a need to add more memory to your system to reduce the number of page faults and improve performance.

Memory – Available M Bytes

SQL Server: Buffer Manager – Page Life Expectancy (Higher is good)

SQL Server: Memory Manager – Memory Grants Pending (Lower is good)

SQL Server: Buffer Manager, Buffer Cache Hit Ratio (higher is better)

SQL Server: Memory Manager – Target Server Memory

SQL Server: Memory Manager – Total Server Memory

SQL Server: SQL Statistics – Batch Requests/sec

SQL Server: SQL Statistics – Compilations/sec

Hence from the Capacity Planning point of view from the performance perspective ,keep watch for an upward trend in the value of Memory\Pages/Sec and add the memory when paging operation absorb more than 20-50 % of your total disk I/O bandwidth

Once a while this kind of alert from SQL is ok you can monitor for some time and resolve if all goes fine, but if the alert is frequent then you have to investigate on the above counters because if could an issue of memory crunch\bottleneck and I would say the trend for these counter values is more important than their absolute value at any given time and If you notice that the average Page Life Expectancy is dropping over time, that is quite significant indication(and not in a good condition).

Conclusion: Don’t ignore this alert if it’s coming frequently from certain servers and during that time on focus on trend obtained from above counters (You can ignore this alert if you are aware that during certain time some heavy activity is going on server and because of that this alert appears and off Couse if customer is not complaining). Also If you are sure that for some server you can’t do anything because their hardware lifecycle is over and you don’t want to spend too much on them then define your threshold again or override this alert with the help of SCOM team in your environment, otherwise if you feel that it’s actually a serious issue on server on the basis of above counters then ask customer to add more memory on their dedicated server but make sure before asking him to add more memory although these days the memory chips are quite cheap but still there is a cost involve.


How to extract historical deadlock from extended events



SET@ring= (SELECT     cast(xet.[target_data]AS[xml])

                        FROM       sys.dm_xe_session_targetsxet



                        WHERE      xe.[name]=’system_health’);


SELECT                                                                                                                AS’DEADLOCKS’,


                                OVER (

                                        ORDERBYsyshealth.xevent.value(‘(@timestamp)’,’DATETIME’))           AS’Sequence’,


                                                                   ‘DATETIME’)                                    AS’Deadlock time’,

                        –SysHealth.XEvent.query(‘.’) AS [DeadlockEvent],



                                                                                ‘NVARCHAR(MAX)’)ASxml)         AS’Deadlock graph’

–SysHealth.XEvent.value(‘data[1]’,’NVARCHAR(MAX)’) AS DeadlockGraph

FROM        (SELECT@ringASring)ASbuffer

CROSSapplyring.nodes (‘//RingBufferTarget/event’)ASsyshealth (xevent)

WHERE       syshealth.xevent.value(‘(@name)[1]’,

                                                                   ‘varchar (100)’)=’xml_deadlock_report’

ORDER       BY[deadlock time]DESC;

When was the last time DBCC CHECKDB executed on my databases


ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN (‘dbi_dbname’))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN (‘dbi_dbccLastKnownGood’)
SELECT CHECKDB1.Value AS DatabaseName, replace(CHECKDB2.Value,’1900-01-01 00:00:00.000′,’Never Run’) AS LastRanDBCCCHECKDB
ON rn1 =rn2


Capture lead blocker\blocking details in SQL 2008 along with SQL Commands exceeding 60 seconds

It is very hard to find good and bad blocking, in case you need to capture blocking in your database which is exceeding a specific timeframe than below mentioned script can be used.

Step 1 Create Table to store blocking information










CREATE TABLE [dbo].[CatchBlocking](

      [CaptureDate] [datetime] NULL,

      [session id] [smallint] NULL,

      [login] [nvarchar](256) NULL,

      [database] [nvarchar](256) NULL,

      [task state] [nvarchar](120) NULL,

      [command] [nvarchar](32) NULL,

      [application] [nvarchar](256) NULL,

      [head blocker] [varchar](1) NULL,

      [login time] [datetime] NULL,

      [last request start time] [datetime] NULL,

      [monitoring time] [datetime] NULL,

      [blocked minutes] [nvarchar](256) NULL,

      [host name] [nvarchar](256) NULL,

      [text] [nvarchar](max) NULL










Step 2 Insert the below code in a job step which will execute after every minute



DECLARE @loginname         sysname,

            @databasename      sysname,

            @hostname          sysname,

            @userprocessesonly char(1),

            @headblocker       nvarchar(1),

            @minutesofblocking int,

            @monitortime       datetime


— Filter your monitoring here!


SET @loginname = ”

SET @databasename = ”

SET @hostname = ”

SET @userprocessesonly = ‘Y’ –Y

SET @headblocker = ‘1’ — 1

SET @minutesofblocking = ’60’ — 1

–Blocking can be captured in terms of minutes and seconds, below in the script use ss\minutes accordingly to

–capture blocking in minutes\seconds



SET @monitortime = getdate()

INSERT INTO dbo.CatchBlocking


                         [session id] = s.session_id,

                        —  [User Process]  = CONVERT(CHAR(1), s.is_user_process),

                        [login] = s.login_name,

                        [database] = isnull(db_name(p.dbid),


                        [task state] = isnull(t.task_state,


                        [command] = isnull(r.command,


                        [application] = isnull(s.program_name,


                        [head blocker] = CASE

                                                       — session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

                                                       WHEN r2.session_id IS NOT NULL AND

                                                              (r.blocking_session_id = 0  OR

                                                               r.session_id IS NULL) THEN ‘1’

                                                       — session is either not blocking someone, or is blocking someone but is blocked by another party

                                                       ELSE ”


                        [login time] = s.login_time,

                        [last request start time] = s.last_request_start_time,

                        [monitoring time] = @monitortime,

                        [blocked minutes] = cast(datediff(ss,s.last_request_start_time, @monitortime) AS int),

                        [host name] = isnull(s.host_name,


                        st.text AS ‘Text’

FROM            sys.dm_exec_sessions s

LEFT OUTER JOIN sys.dm_exec_connections c

                   ON (s.session_id = c.session_id)

LEFT OUTER JOIN sys.dm_exec_requests r

                   ON (s.session_id = r.session_id)

LEFT OUTER JOIN sys.dm_os_tasks t

                   ON (r.session_id = t.session_id AND

                         r.request_id = t.request_id)


                        — In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as

                        — waiting for several different threads.  This will cause that thread to show up in multiple rows

                        — in our grid, which we don’t want.  Use ROW_NUMBER to select the longest wait for each thread,

                        — and use it as representative of the other wait relationships this thread is involved in.

                        SELECT *,


                                       OVER (

                                             partition BY waiting_task_address

                                             ORDER BY wait_duration_ms DESC) AS row_num

                         FROM   sys.dm_os_waiting_tasks) w

                   ON (t.task_address = w.waiting_task_address) AND

                        w.row_num = 1

LEFT OUTER JOIN sys.dm_exec_requests r2

                   ON (s.session_id = r2.blocking_session_id)

LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g

                   ON (g.group_id = s.group_id)

LEFT OUTER JOIN sys.sysprocesses p

                   ON (s.session_id = p.spid)

CROSS apply     sys.dm_exec_sql_text(r.sql_handle) AS st



P.dbid in (select dbid from  sys.sysdatabases where name like ‘%IDEA%’)    



                              (@loginname = ”  OR

                         s.login_name = @loginname) AND

                        (@databasename = ”  OR


                                    N”) = @databasename) AND

                        (@hostname = ”  OR

                         @hostname = isnull(s.host_name,

                                                      N”)) AND

                        (s.is_user_process = CASE

                                                             WHEN upper(@userprocessesonly) = ‘Y’ THEN 1

                                                             ELSE s.is_user_process

                                                       END) AND

                        (@headblocker = ”  OR

                         @headblocker = CASE

                                                      — session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

                                                      WHEN r2.session_id IS NOT NULL AND

                                                             (r.blocking_session_id = 0  OR

                                                              r.session_id IS NULL) THEN ‘1’

                                                      — session is either not blocking someone, or is blocking someone but is blocked by another party

                                                      ELSE ”

                                                END) AND

                        datediff(ss,s.last_request_start_time,@monitortime) >= @minutesofblocking AND

                        (st.text NOT LIKE ‘%BACKUP%’ AND st.text NOT LIKE ‘%RESTORE%’)

ORDER           BY s.session_id


— If there is an unreported head blocker session let’s send a new mail.

IF EXISTS (SELECT hbs1.[session id]

               FROM   dbo.CatchBlocking hbs1

               WHERE  cast(hbs1.[monitoring time] AS nvarchar(256)) = cast(@monitortime AS nvarchar(256)) AND

                          hbs1.[session id] NOT IN (SELECT hbs2.[session id]

                                                                  FROM   dbo.CatchBlocking hbs2

                                                                  WHERE  cast(hbs2.[monitoring time] AS nvarchar(256)) < cast(@monitortime AS nvarchar(256)) AND

                                                                           hbs2.[session id] = hbs1.[session id] AND

                                                                           hbs2.[last request start time] = hbs1.[last request start time] AND

                                                                           hbs2.text = hbs1.text))



select * from dbo.CatchBlocking


Capture blocking details in SQL 2005 along with SQL Commands for both SPID and Blocked

If you want to store all blocking details in your databases in SQL Server 2005 then you can use the below method.

Step 1 Run the below script to create a table







CREATETABLE [dbo].[MyBlockingInfo](

[CaptureDateTime] [datetime] NOTNULL,

[spid] [smallint] NOTNULL,

[kpid] [smallint] NOTNULL,

[blocked] [smallint] NOTNULL,

[waittype] [binary](2)NOTNULL,

[waittime] [bigint] NOTNULL,

[lastwaittype] [nchar](32)NOTNULL,

[waitresource] [nchar](256)NOTNULL,

[dbid] [smallint] NOTNULL,

[uid] [smallint] NULL,

[cpu] [int] NOTNULL,

[physical_io] [bigint] NOTNULL,

[memusage] [int] NOTNULL,

[login_time] [datetime] NOTNULL,

[last_batch] [datetime] NOTNULL,

[ecid] [smallint] NOTNULL,

[open_tran] [smallint] NOTNULL,

[status] [nchar](30)NOTNULL,

[sid] [binary](86)NOTNULL,

[hostname] [nchar](128)NOTNULL,

[program_name] [nchar](128)NOTNULL,

[hostprocess] [nchar](10)NOTNULL,

[cmd] [nchar](16)NOTNULL,

[nt_domain] [nchar](128)NOTNULL,

[nt_username] [nchar](128)NOTNULL,

[net_address] [nchar](12)NOTNULL,

[net_library] [nchar](12)NOTNULL,

[loginame] [nchar](128)NOTNULL,

[context_info] [binary](128)NOTNULL,

[sql_handle] [binary](20)NOTNULL,

[stmt_start] [int] NOTNULL,

[stmt_end] [int] NOTNULL,

[request_id] [int] NOTNULL




Step 2 Run the below script to create a table to capture SQL Command






[CPDATETIME] [datetime] NULL,

[SPIDTXT] [nvarchar](4000)NULL,

[BKSPIDTXT] [nvarchar](4000)NULL


Step 3 Run the below script to create trigger on table MyBlocking






ON [dbo].[MyBlocking]













set @CPDATE=(select CAPTUREDATETIME from INSERTED wheredbid=13)

set @SPID=(select SPID from INSERTED wheredbid=13)

set @BKSPID=(select BLOCKED from INSERTED wheredbid=13)

— this will give you the SQLHandle for the culprit SPID



— this statement will give you the SQL Statement for culprit SPID






Step 4 Run the below script to create a table

Create 2 jobs with an interval of 30 seconds inserting sysprocesses information in MyBlocking table.

@command=N’Insert into dbo.MyBlocking

select getdate(),* from sys.sysprocesses where blocked <>0′,

When the above jobs execute they will insert the blocking information in your blocking table MyBlocking and then the trigger will capture the details of running process and the blocking process in another table for future references and output will be shown as below