Amazing SQL server tools every DBA must have!!! – Part 2

This post is continuation of my post Post

In the previous post, I discussed about Database and OS configuration tools and in this post I will discuss about performance monitoring tools.

Performance monitoring and troubleshooting : In performance monitoring we have a great set of tools which are free of cost and can be easily accessible.  I won’t go in much details of these tools as there is already lots of stuff available. The main intent is to collate all the tools list at one place. It will help to know what tools are available for free and how best they can fit in your environment.

Performance monitoring and troubleshooting can be done in two ways. 1. Live 2. postmortem.

lets take an example of High CPU issue, if DBAs are asked to troubleshoot live on such issues – they need a very deep understanding of how SQL server work and need lots of SQL scripts/perfmon counters handy to find out the issue. But for DBAs who have level 100-200, they will end up either restarting the SQL server or killing the sessions with heavy queries.

Now, lets say the issue is not fixed in live troubleshooting and the management team wants to know the root cause of the issue i.e. postmortem analysis. If DBA know lots of DMV’s it still can be figured out though still the chances are very less but for the new DBA it will be very difficult to find out something.

To cater to both the needs of live and postmortem analysis, we have enough tools available.

For postmortem performance troubleshooting (These tools can be used for performance baseline as well) , the tools which we have are :
1.MDW – Management Data Warehouse
2.PSSDIAG/SQLNEXUS
3.PAL

For Live troubleshooting and monitoring the tools we have, are:
1. Activity monitor
2. UCP
3. Standard SQL reports

Postmortem performance analysis tools

1. Management Data Warehouse (MDW) : – This was the new tool introduced in SQL server 2008. It’s gained a lot of popularity for the postmortem analysis. Basically, It has predefined set of data collectors which can be used to collect and upload the data to MDW database.  It captures query statistics,disk activity and server activity. Even Utility control point(will discuss in the next sections) tool uses this in the background.

Here are the reports screenshot which could be used to find out the cause of the issue:

Overall Server Summary:

clip_image002

Query Statistics report where you could see the reports based on CPU/IO/Duration.

image

Disk summary report

image

These reports help to find out the issue proactively. After studying it little deeper, I found that they have SSIS packages built to fetch the data from the server and upload it to the data warehouse database.

To read the packages in detail you could open and fetch the packages from SQL server integration services and understand what it does:

image

Note:  Change the job frequency accordingly as the default frequency may not be appropriate for your environment.

2. PSSDIAG / SQLNexus : This is the set of tools which helps to do postmortem analysis. I will discuss briefly about both of them.

PSSDIAG: It’s a framework you could configure the kind of data you want to capture.  The dashboard looks like:
image

In the screenshot, you could see various sections like profiler trace, perfmon, SQLDIAG and other SQL scripts.  In this blog, I have discussed about how to configure the pssdiag for the high CPU issue: https://dbcouncil.net/2012/02/18/configure-pssdiag-to-capture-high-cpu-issues/ . you could refer this blog for reference to configure the tool.

Once the tool is configured and you save the configuration, it will create a pssd.cab/exe which could use to capture the logs. You have to follow the below steps to run it during the time of the issue:

Steps to run the PSSDIAG:

a. Create a folder named PSSDIAG on your SQL server machine.  This folder should be on a drive with plenty of space as diagnostic file collections can be quite large
b. Download the PSSDIAG from FTP site.
c. Open a command prompt.  Change the current directory to your PSSDIAG folder and run pssd.exe to extract its contents.
d. Run PSSDIAG.cmd at the command prompt to start the collection process.
e. Once PSSDIAG displays the message, “PSSDIAG Started,” attempt to reproduce your issue.
f. Stop PSSDIAG by pressing CTRL+C.

Please make sure you collect the data for 10-15 minutes minimum to get the complete understanding of the issue.

Note : – Please make sure you configure profiler traces to minimal level. It can cause production outage if not configured appropriately.

Reference links:
Freely downloadable tool, the PSSDIAG: – http://diagmanager.codeplex.com/
Configure PSSDIAG : – http://sql-blogs.com/2012/02/18/configure-pssdiag-to-capture-high-cpu-issues/
More information on PSSDIAG: – http://support.microsoft.com/kb/830232

After collecting the logs ,you may think – how to analyze the data. To help us with that, there is another tool SQLNEXUS.

SQLNexus :  SQLNEXUS is tool which helps to analyze the logs captured by PSSDIAG utility. The PSSDIAG logs are uploaded to SQLNEXUS tool and it shows the performance bottlenecks, slow queries, blocking in graphical reports. It provides effective and detailed analysis of the issue, if any.

We have to upload the PSSDIAG data on the dashboard like this: Click on import and give the path of the PSSDIAG output folder:

image

Reports of SQLNexus look like:

image

Steps to install and run the SQLNEXUS tool:

Download RML – http://sqlnexus.codeplex.com/wikipage?title=ReadTrace&referringTitle=Home
Download SQLNexus –  http://sqlnexus.codeplex.com/
How to run SQLNexus – http://sqlnexus.codeplex.com/wikipage?title=GETTING_STARTED

For manual PSSDIAG analysis, you could follow this blog: http://sql-blogs.com/2012/03/23/analyzing-pssdiag-logs-without-using-sqlnexus/

Above mentioned tools are boon to the DBAs to find out RCA. If you are confused about when to use which tool , let me explain you little more about it:

MDW : – This tool can be left configured and can be used for post mortem analysis. The limitation of this tool is that, it won’t give you missing indexes list , complilation / recompliation details etc. It will give you enough details to find out the issue but if you want to go in granular level, then this tool may not give you complete information.

To get more details , you will need to run the PSSDIAG. To run the PSSDIAG, you will have to first install the dashboard where you will configure the package. Once the configuration is done, you could pickup that .cab/exe file and take it to the server where you want to run it.

There you will follow the steps mentioned in PSSDIAG section above and capture the data. Once the data is captured you will need to feed that to SQLNexus so that it could show the reporting in more detailed format to find out the issue.

For manual PSSDIAG analysis, you could follow this blog: https://dbcouncil.net/2012/03/23/analyzing-pssdiag-logs-without-using-sqlnexus/

The drawback of PSSDIAG over MDW is , you will have to run it manually every time when the issue occurs but MDW will keep running in the background and fetch the data for you.

Live troubleshooting tools :

Activity Monitor : This is a very good tool to troubleshoot live issues. It has four sections as shown the below screenshot:

image

To see this tool you need to right click on the instance name and select activity monitor:

image

It can be used best for blocking issues or may be other query issues. Under processes you can get the output of sys.dm_exec_requests joined with some other DMVs which can be traced in profiler trace or we can check the query text:

image

Same way, we could use the other options to get resource waits , IO stats and expensive queries. It can help to at least find out the direction , you need to go.

It will be more effective if used with the standard reports mentioned below:

Standard SQL reports : There is a great set of reports which could be leveraged to do live troubleshooting for performance issues. Here is how find them:

Right click on the instance name as shown in the screenshot:
image

here is the screenshot of Server Dashboard report :
image

Similarly, there are lots of other reports which can help to find out high CPU/IO/time queries.  To read more about it , please check : http://sudeeptaganguly.wordpress.com/2011/11/04/standard-reports-in-ssms-2008-r2/ and  http://blogs.msdn.com/b/buckwoody/archive/2008/03/10/sql-server-management-studio-standard-reports-login-failures.aspx

UCP (Utility Control point): I have seen very few DBAs aware of this utility but can be very useful to track CPU,DB space and disk space on the servers.

Let me explain the best use of this utility by giving an example. lets say you have 10 applications hosted on SQL environment and there is high CPU caused by one of them. Could you think of any method of tracking which application’s query is causing the issue.

May be the answer will be, find out the query causing the issue and then find out which application this query belongs to. But If we use UCP , we can find out the application wise CPU consumption with just few clicks. But we will have deploy the application using data tier project using BIDS then only we will get the list under data tier application.

Application wise resource usage as shown in below screenshot :

image

This article explains about how to configure the UCP and it’s benefits : http://www.databasejournal.com/features/mssql/sql-server-utility-control-point-ucp.html

The dashboard of UCP looks like :

image

I have got this question many times, We have all the tools available then why do people go for paid tools for DB monitoring. The answer is , the paid tools give us one integrated framework where we can monitor all this. But almost same work can be accomplished by using all the above mentioned tools but the only difference is we have to hop between these tools to drive a conclusion.  These tools are boon for the organizations which don’t have budgets to spend on the monitoring tools.

HTH!

Amazing SQL server tools every DBA must have!!! – Part 1

After visiting to many customer locations I have found there is a great set tools , which many DBAs are not aware of. They struggle a lot while troubleshooting performance issues. So, I decided to blog on the subject matter so that it could help DBAs to troubleshoot the performance issues faster. This blog targets the audience with the knowledge level 100-200. All the tools discussed in this article are either free or inbuilt in SQL server installation:

There are majorly two sections of this blog:

    1. Database and OS configuration
    2. Performance monitoring

Database and OS configuration : There are 2 major tools which we have available :

  • BPA(Best Practice Analyzer): BPA tool is used to find the discrepancies in the system and SQL level configuration. It helps to unveil the critical issues before it causes serious outage. It has some great set of rules which it runs against the collected data. This tool is deprecated in SQL 2012 as it’s taken over by system center advisor which we will discuss in the next section:

It shows critical errors : If consistency checks are not being done on regular basis, If backups are outdated and there are some critical issues in the server etc. Please check the demo in the links mentioned below to get familiarity in depth:

This tool can be great help to understand the issues proactively and take necessary actions before it’s too late.

Here is the screenshot of the recommendations by BPA:

clip_image001

For more details check the link:

Download and Install MBCA  –http://www.microsoft.com/en-us/download/details.aspx?id=16475

Download and install BPA – http://www.microsoft.com/en-us/download/details.aspx?id=15289

Steps to run BPA – http://www.microsoft.com/en-us/download/details.aspx?id=436

Demo of how BPA works  – http://www.microsoft.com/en-us/download/details.aspx?id=18882

 

System Center Advisor(SCA) : This tool , which is now released for public , helps to identify the discrepancies in the SQL configuration. Not only this, it can help to find out the unsupported features and patch levels of SQL server instances. This tool can be of great help for the customers who can’t purchase the Microsoft premier services.

It pretty much comes under the proactive work for the SQL environment. As more than 30% approx. the performance issues in the SQL environment are due to the wrong configuration. It also helps to proactively find out, if the backups are outdated or consistency checks are not done etc.

How it works: 

There are two major components of this tool :

1. Gateway
2. Agent

1. Gateway: – This component has to be installed on the machine from where you will upload the logs to the MS cloud service. It can be a centralized place from where the data is uploaded to the cloud service or can be installed on every individual machine to upload.

2. Agent : – This component must be installed on the machine on which you want to collect the data.

image

This is how the reports look like :

image

image

In most of the environments, the production servers can’t be exposed to internet and the ports can’t be opened without critical purpose. For this tool to upload the data to cloud service ,we need to open a port (port no. 80) . To avoid any complications there is a provision to setup a remote gateway to which all the data will be provided and it will upload the logs to the cloud service.  As shown the first screenshot under this section, multiple agents can report to one gateway and it can be given internet access to upload the logs.

To read more about it , please check this post: http://sql-blogs.com/2013/03/21/system-centre-advisor-is-now-free/

Performance monitoring, I will discuss in my next post. Please click here to read about it

Configure PSSDIAG to capture high CPU issues

I am sure most of the SQL community is aware of the PSSDIAG and how helpful it is when it comes to troubleshooting performance issues. In this blog , I will discuss about how to configure the PSSDIAG in high CPU Scenarios.

Here are few blogs which I would like to share about how we can download and configure PSSDIAG:

Download PSSDIAG: – http://diagmanager.codeplex.com/releases/view/67449

Configure PSSDIAG :- http://sqlbg.wordpress.com/2011/06/15/pssdiag-and-sqldiag-manager/
http://troubleshootingsql.com/2011/05/25/tools-tips-and-tricks-9-pssdiag-configuration-manager/

Please be aware, this PSSDIAG is only for SQL server 2005,2008 and for earlier version you will still need to use sqldiag :- http://support.microsoft.com/kb/830232

Here are the things which we can configure in the PSSDIAG(SQL database engine):

1. Profiler Traces
2. SQL DMV outputs
3. Perfmon

Before configuring the PSSDIAG, we have to be very clear about the workload on the environment especially when it’s production. When we run the PSSDIAG, there can be slight performance degradation and if the workload is very high then it can even cause outage (if not configured properly). The heaviest resource taker in the PSSDIAG is profiler traces and in busy environments they can be disabled to avoid any outage/issue.

There may be a question in your mind – How to differentiate between low and high workload? Well, this question is open ended and different people may have different answers.  Here is what I think about it (completely based on my experiance)

Low Workload : – less than 100 batches per second
Medium Workload : – 100 – 500(approx.) batches per second
High Workload: – greater than 500 batches per second

The above is just a rough idea but any load even with less than 100 batches can be a high load depending upon the type of queries and throughput of the system. So, the configuration with the profiler traces needs some logic otherwise the configuration is very simple :

1. Select the architcture X64,X86 or IA64
2. Select Instance type(SQL 2005 or SQL 2008)
3. Enter machine name and instance name
4. Uncheck the SQL trace button

5. Select the files shown in the screen shot(SQL 2008 Perf Stats,SQL 2005 Perf Stats,SQL memory error,SQL blocking) and leave rest of the things to default unless there is any specifc requirement.

Here you go, PSSDIAG without traces for high CPU is ready. This approach works for heavy load environments especially for SQL server 2008. Because in SQL 2008, we can find out the high CPU queries accurately even without having profiler traces. The secret behind this the new feature called Plan hash and query hash which was introduced in SQL server 2008.

I am sure you will be aware of plan hashes(only SQL 2008 onwards): –  http://blogs.msdn.com/b/bartd/archive/2008/09/03/query-fingerprints-and-plan-fingerprints_3a00_-the-best-new-sql-2008-feature-you_2700_ve-never-heard-of.aspx (must read). And yes, we capture plan hashes in the PSSDIAG for SQL 2008 and which eliminates the need of profiler traces .

But for SQL 2005, we have to rely on sys.dm_exec_query_stats and as mentioned in the above blog, it’s not 100% correct. But it gives us fair idea of which query may be the culprit.

Now, what if we need to configure the PSSDIAG along with the profiler traces, here are the steps for that:

1. Select the architcture X64,X86 or IA64
2. Select Instance type(SQL 2005 or SQL 2008)
3. Enter machine name and instance name
4. In profiler traces, here are the events you could capture(Specifc for High CPU only).

5. Select the files shown in the screen shot(SQL 2008 Perf Stats,SQL 2005 Perf Stats,SQL memory error,SQL blocking) and leave rest of the things to default unless there is any specifc requirement.

Once this is configured, we are ready with the PSSDIAG to be run.  For various options on how PSSDIAG can be scheduled to run at specifc times, please check the article : – http://support.microsoft.com/kb/830232. I have created the PSSDIAG for SQL server 2008 and 2008 R2. Please copy the files , save them as .xml and open in the PSSDIAG by clicking on open button on the top left( PSSDiag_2008,PSSDiag_2008R2). Please test it before running it on production server.

In my next post, I will explain how can we analyze the PSSDIAG output logs without using SQL Nexus and what are the main files that we have to look at while troublehoosing high CPU issues.

Configure SQL agent alerts to notify the performance issues

In every production environment, we have to monitor the various aspects of the SQL instance proactively to avoid any outage. Many times there are requirements like

Whenever the size of database increases more than a specific size- alert me (email/text me)Whenever the procedure cache size increases more than a specific size –alert me
Whenever Page Splits/sec increases more than a specific no. – alert me
and so on.

Today, we are going to talk about how we can achieve this using SQL agent alerts. It comes very handy when we don’t want to write a T-SQL code or use any third party tool.

In SQL server agent we can create 3 types of alerts:
1. SQ: server event alert
2. SQL server performance condition alert
3. WMI event alert

In this blog I will talk only about the second one i.e. SQL server performance condition alert.

To start with SQL server performance condition alert, I am sure everyone would have heard of the DMV sys.dm_os_performance_counters. In this DMV’s output we get the performance monitor data of SQL server. Yes, that’s what SQL agent use/query in the background.

1) Here is how you select this alert: right click on the alert and select new alert.

agent_alert

Then select SQL server performance condition alert.

2) click on the object combo

agent_alert_object

and select the counter you are looking for – to monitor.

3) click on the counter

agent_alert_object_counter

4) select the instance for which you want to monitor and by default it’ll be the one from which you are creating the alert.

5)  select the condition from the “alter if counter” combo and then set the value.

Once this is configured then the other options which are 1. response 2. options can be configured as mentioned in the article: – http://msdn.microsoft.com/en-us/library/ms191508.aspx

One more important thing which you may be thinking is how this type of alert works. As per my testing on this , I came to know that when we configure the alert it makes an SQL query using the sys.dm_os_performance_counter DMV and runs it every 20s. Once the condition which you had set becomes true then the notifications are sent.

Even, you can execute the job in response to the alert along with the notification.

alert_response

e.g. if space in the database goes below the threshold , I want to add another .ndf file to the database and also send a notification.

HTH

Troubleshoot SQL server high CPU issue without using profile traces

The scope of these issues is very wide but I would discuss about the approach which I follow to resolve these issues. I have resolved more than 50% of the cases by using this approach without even looking at profiler traces. I hope this will help you troubleshooting issues in your environment.

It’s specific to SQL server 2008 – 64 bit environments but could be tried on 32 bit environments too.

Let’s discuss about how to start it step by step.

Step 1: – When you see high CPU utilization on the server check in task manager, which process is consuming high CPU. If it’s SQL server then open perfmon and add the counter for Processor: % Privileged time, % Processor time and %user time

If you see the %privileged time to be high like hovering around 40% and above then it could be some driver issue. To troubleshoot these kinds of issues you could try:

Using Xperf: – http://blogs.msdn.com/b/ntdebugging/archive/2010/03/22/using-xperf-to-root-cause-cpu-consumption.aspx — Video; http://msdn.microsoft.com/en-us/performance/cc709422

If it’s user time which is high then find the process which is consuming high CPU. If it’s SQL server which is consuming high CPU then the below steps will be applicable for troubleshooting:

Step 2: – First and very important one is to know what’s the architecture i.e. 64 bit (X64 or IA 64) or 32 bit(x86):

Check the configuration using command: – Xp_msver

In this blog I will talk specifically about SQL server 2008 64-bit.

Step 3: – Check if there are any trace flags enabled:

dbcc tracestatus (-1); It’s always good to check the reason of enabling the trace flags, if enabled.

Step 4: – Check the below mentioned SQL parameters

sp_configure'advanced',1
go
reconfigure with override
go
sp_configure
go

  1. Priority      Boost: – it’s      not recommended to be enabled as it can cause high CPU utilization. If      it’s enabled (value =1) please disable(set value to 0) it – it’s a static      parameter which needs SQL restart
  2. Max server memory : – If we have 64-bit architecture, please make sure you have set the value for it leaving 3-4 GB for the OS. This sometimes contributes indirectly to cause high CPU.
  3. Max degree  of parallelism: – Although this parameter doesn’t make much of a      difference but sometimes I have seen SPID stuck in      ACCESS_METHODS_SCAN_RANGE_GENERATOR, cxpacket or other parallel waittypes.      If you see any such waits when you run this query :Select a.session_id, a.wait_type, a.wait_time, a.* from sys.dm_exec_requests a, sys.dm_exec_sessions b where a.session_id=b.session_id and b.is_user_process=1

If you don’t see cxpacket or parallel waits, please make sure you set the MAXDOP as per the recommended practices:-  http://blogs.msdn.com/b/sqltips/archive/2005/09/14/466387.aspx

If in your case waittypes to be any of related to parallelism and the waittime is more than 20 ms reduce the parallelism to e.g. if 8 then to 4 and so on unless the waits go away.

Please note: – Changing the MAXDOP because of cxpacket waits is a temporary solution. 

Step 5: – Run DBCC memorystatus and check for the

1. USERSTORE_TOKENPERM memory clerk. If the value is high say above 400 MB then follow the article: –

http://blogs.msdn.com/b/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx
http://support.microsoft.com/kb/955644

2.  CACHESTORE_SQLCP memory cachestore : – This cachestore is responsible to keep the execution plans for ad-hoc SQL queries,autoparameterized plans, and prepared plans. If the size of this cache becomes abnormally high, then there are fair chances of huge number of queries getting compiled because no plan reuse.  In PSSDIAG logs, we have called it as procedure cache pollution. If this is consuming lots of memory then follow step 6.

3. MEMORYCLERK_SQLQERESERVATIONS memory clerk : – This memory clerk is use for hashing and sorting by SQL server and if the memory seems less the resultsets are spilled over to tempdb while executing query. If the result sets spill to tempdb, we check in  perfmon  for  object – Access methodsWorktables/sec (for sorting)  and  Access methodsWork files/sec(hashing). If this memory clerk is consuming lots of memory then follow step 7.

Step 6
: – If  CACHESTORE_SQLCP  is high then next thing we have to check is compilations and recompilations :

  1. Open      perfmon and add the counters: –

MSSQL: SQL statistics à Batch requests/s, compilations/s and recompilations/s.

If you notice compilations/sec are greater than 20% then there is definitely fair amount of chances for Procedure cache pollution. To confirm that please run the query:

WITH cached_plans (cacheobjtype, objtype, usecounts, size_in_bytes, dbid, objectid, short_qry_text) AS
(
SELECT p.cacheobjtype, p.objtype, p.usecounts, size_in_bytes, s.dbid, s.objectid,
CONVERT (nvarchar(100), REPLACE (REPLACE (
CASE
-- Special cases: handle NULL s.[text] and 'SET NOEXEC'
WHEN s.[text] IS NULL THEN NULL
WHEN CHARINDEX ('noexec', SUBSTRING (s.[text], 1, 200)) > 0 THEN SUBSTRING (s.[text], 1, 40)
-- CASE #1: sp_executesql (query text passed in as 1st parameter)
WHEN (CHARINDEX ('sp_executesql', SUBSTRING (s.[text], 1, 200)) > 0)
THEN SUBSTRING (s.[text], CHARINDEX ('exec', SUBSTRING (s.[text], 1, 200)), 60)
-- CASE #3: any other stored proc -- strip off any parameters
WHEN CHARINDEX ('exec ', SUBSTRING (s.[text], 1, 200)) > 0
THEN SUBSTRING (s.[text], CHARINDEX ('exec', SUBSTRING (s.[text], 1, 4000)),
CHARINDEX (' ', SUBSTRING (SUBSTRING (s.[text], 1, 200) + ' ', CHARINDEX ('exec', SUBSTRING (s.[text], 1, 500)), 200), 9) )
-- CASE #4: stored proc that starts with common prefix 'sp%' instead of 'exec'
WHEN SUBSTRING (s.[text], 1, 2) IN ('sp', 'xp', 'usp')
THEN SUBSTRING (s.[text], 1, CHARINDEX (' ', SUBSTRING (s.[text], 1, 200) + ' '))
-- CASE #5: ad hoc UPD/INS/DEL query (on average, updates/inserts/deletes usually
-- need a shorter substring to avoid hitting parameters)
WHEN SUBSTRING (s.[text], 1, 30) LIKE '%UPDATE %' OR SUBSTRING (s.[text], 1, 30) LIKE '%INSERT %'
OR SUBSTRING (s.[text], 1, 30) LIKE '%DELETE %'
THEN SUBSTRING (s.[text], 1, 30)
-- CASE #6: other ad hoc query
ELSE SUBSTRING (s.[text], 1, 45)
END
, CHAR (10), ' '), CHAR (13), ' ')) AS short_qry_text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) s
)
SELECT COUNT(*) AS plan_count, SUM (size_in_bytes) AS total_size_in_bytes,
cacheobjtype, objtype, usecounts, dbid, objectid, short_qry_text
FROM cached_plans
GROUP BY cacheobjtype, objtype, usecounts, dbid, objectid, short_qry_text
HAVING COUNT(*) > 100
ORDER BY COUNT(*) DESC
RAISERROR ('', 0, 1) WITH NOWAIT

In the output you will need check:

Plan_count : – number of times the similar plan was found in the procedure cache

use count : – How many times the plan was reused and in the output of this query you will see the use_count = 1 most of the times which will tell that the query was not being reused.

Total_size_in_bytes: – It’s the memory consumed by the execution plans of the similar kinds.

For the description of the counters please check: – http://msdn.microsoft.com/en-us/library/aa173933(v=sql.80).aspx

In many cases you will find this to be the problem.

If you see the procedure cache pollution then you need to check the memory clerk: CACHESTORE_SQLCP in “dbcc memorystatus” command output (run in query analyzer).

In 64 bit system I have seen this memory to grow up to 8GB or more which is sign of excessive compilation. After you confirm this, there can be two possibilities

  1. Memory      pressure which can cause cleanup of the execution plans and make SQL to      compile the plan and in this case there will be no procedure cache      pollution.
  2. There are      excessive Ad-hoc queries which are causing this compilation and you will      see high pollution in the queries.

For point number 2, there are steps which could be followed:

  1. Identify      the text and database of the query which is being compiled in the output      of the above mentioned query and set parameterization to forced
  2. Parameterize      the ad-hoc query :- http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

If you see high re-compilations then check the article : – http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx .

Also, make sure statistics of the tables are updated with fullscan. Query for that is:
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

To check the statistics updated date, run the query:

EXEC sp_msforeachtable ‘select object_name(object_id) tablename,name as index_name,stats_date(object_id,index_id) as statistics_update_date from sys.indexes where Object_id=object_id(“?”)’

If after implementing the above action plans issue doesn’t resolve then:

7.  Check for the hashing and sorting in SQL server:

Open perfmon and add the counters: – MSSQL: memory manager (Granted workspace area) or check the output of the query:

select * from sys.dm_os_memory_clerks where type=’MEMORYCLERK_SQLQERESERVATIONS’

If the value is very high e.g. more than like 3-4 GB and I have seen this value to go till 10 GB in high memory machines. It’s a clear sign of big hash tables /huge sorting being done.

Then run the query (only for sql server 2008) : –

  1. Run the query :

print '-- top 10 CPU by query_hash --'

select getdate() as runtime, * into tbl_QueryHashByCPU

from

(

SELECT TOP 10 query_hash, COUNT (distinct query_plan_hash) as ‘distinct query_plan_hash count’,

sum(execution_count) as ‘execution_count’,

sum(total_worker_time) as ‘total_worker_time’,

SUM(total_elapsed_time) as ‘total_elapsed_time’,

SUM (total_logical_reads) as ‘total_logical_reads’,

max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1,

CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text]))

ELSE qs.statement_end_offset/2 – qs.statement_start_offset/2 + 1

END), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘)) AS sample_statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

group by query_hash

ORDER BY sum(total_worker_time) DESC

) t

go

select * from tbl_QueryHashByCPU
go

In the above query’s output you will get to know the TOP CPU query. You can check the execution plan of the query by running the query:

select a.query_plan_hash , b.* from sys.dm_exec_requests a

cross apply sys.dm_exec_text_query_plan(a.plan_handle,0,-1) b

where a.session_id=<SPID>  ß Pass the SPID accordingly

Find out which one is on the TOP and then run the below query to find the missing indexes:

DECLARE @runtime datetime

DECLARE @cpu_time_start bigint, @cpu_time bigint, @elapsed_time_start bigint, @rowcount bigint

DECLARE @queryduration int, @qrydurationwarnthreshold int

DECLARE @querystarttime datetime

SET @runtime = GETDATE()

SET @qrydurationwarnthreshold = 5000

PRINT ”

PRINT ‘===============================================================================================’

PRINT ‘Missing Indexes: ‘

PRINT ‘The “improvement_measure” column is an indicator of the (estimated) improvement that might ‘

PRINT ‘be seen if the index was created. This is a unitless number, and has meaning only relative ‘

PRINT ‘the same number for other indexes. The measure is a combination of the avg_total_user_cost, ‘

PRINT ‘avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.’

PRINT ”

PRINT ‘– Missing Indexes –‘

SELECT CONVERT (varchar, @runtime, 126) AS runtime,

mig.index_group_handle, mid.index_handle,

CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)

+ ‘ ON ‘ + mid.statement

+ ‘ (‘ + ISNULL (mid.equality_columns,”)

+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (mid.inequality_columns, ”)

+ ‘)’

+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,

migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

PRINT ”

GO
For information on the DMV’s used in the query, please check http://msdn.microsoft.com/en-us/library/ms345421.aspx

You will definitely find some index recommendations for the TOP queries. Create the indexes and see if that makes the difference.

If creating the indexes doesn’t help then you will have to find the execution plan of the Top CPU query and try to tune it.