Archive for the ‘Monitoring’ Category

Tempdb Growing unexpectadly with recovery model Simple

Short-term fix Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once, will likely cause it to grow again. To shrink tempdb, you can consider using DBCC ShrinkDatabase, DBCC ShrinkFile (for the data or the log file), or ALTER DATABASE. See KB #256650, KB #272318 and KB #307487 for more information. If you can’t shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:


Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with ‘SPID (Server Process ID) : <number>’). Use that <number> in the following:


This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:

Categorias:TemDb Problems

Troubleshoot Sql Server Performance Bottlenecks

Pingback from:

Monitor  Recent CPU Utilization History Trends:

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 2000 record_id, EventTime,
CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
record.value(‘(Record/@id)[1]’, ‘int’) AS record_id,
DATEADD (ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS EventTime,
100-record.value(‘(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS system_cpu_utilization_post_sp2,
record.value(‘(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) AS sql_cpu_utilization_post_sp2 ,
100-record.value(‘(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS system_cpu_utilization_pre_sp2,
record.value(‘(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) AS sql_cpu_utilization_pre_sp2
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
AND record LIKE ‘%<SystemHealth>%’) AS t
) AS t
ORDER BY record_id desc

Monitor  Expensive Queries:

LEFT(P.CACHEOBJTYPE + ‘(‘ +P.OBJTYPE + ‘)’, 35) AS cacheobjtype,
p.size_in_bytes / 1024 AS SIZE_IN_KB,
stat.total_worker_time/1000 AS TOT_CPU_MS,
stat.total_elapsed_time/1000 AS TOT_DURATION_MS,
WHEN PA.VALUE=32767 THEN ‘ResourceDb’
ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname, pa.value))
END, 40) AS dbname,
ELSE REPLACE (REPLACE (sql.[text], CHAR(13), ‘ ‘), CHAR(10), ”)
REPLACE(REPLACE(SUBSTRING (sql.[text], stat.statement_start_offset/2 + 1,
CASE WHEN stat.statement_end_offset = -1 THEN LEN (CONVERT (nvarchar(max), sql.[text]))
ELSE stat.statement_end_offset / 2 – stat.statement_start_offset / 2 + 1 END),
CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS STMT_TEXT,
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sql
OUTER APPLY sys.dm_exec_query_plan(p.plan_handle) as QPLAN
WHERE pa.attribute = ‘dbid’
ORDER BY tot_cpu_ms DESC
SELECT * FROM sys.dm_exec_requests ORDER BY CPU_TIME desc
(select top 50
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc



Management Data Warehouse/ Performance Dashboards


In SQL Server 2008 Microsoft introduced “Management Data Warehouse” (MDW). This blog post should give you a good idea on what to expect from this new feature. Note: All tests and screenshots for this post were done in SQL Server 2008 February CTP (CTP6).

Before starting MDW configuration please make sure that your SQL Server Agent is running. You can configure Management Data Warehouse by going into SQL Server Management studio, then expanding Management folder and right mouse clicking on Data Collection folder and choosing “Configure Management Data Warehouse”. This will start wizard that will ask you MDW database connection information (server, database and authentication). As MDW database puts some overhead for the server, in production environment you probably will want to have this database on a separate server. After wizard completes you should see green arrows for Data collection items:


MDW in SQL Server Management Studio

SQL Server 2008 CTP6 was shipped with 3 data collection sets. These sets run at set frequency and collects data into MDW database. To give you an idea – “Disk usage” collection set runs every 5 seconds, “Query Statistics” collection set runs every 10 seconds and ”Server Activity” collection set runs every 60 seconds. Of course, you can easily change frequency for each collection set in properties.

So what are the results of these collections?

Disk usage

To see disk usage report in SQL Server Management Studio select “Disk Usage” collection set and then right mouse click “Reports”->”Disk Usage Summary”. Here is report from my virtual PC:

DMW Disk usage report

This report shows you database and log size growth trending and if you select a database, you can see even more information about it:

MDW Disk summary report - DB detail

Query Statistics

Query statistics allows you to see reports about query executions. You can choose time interval for your report and then you can see top query report by CPU, Duration, Total I/O, Physical Reads or Logical writes. Here is example of top queries by duration:

Query statistics - report1

And here is example of report you will get when you will select specific query:

Query statistics - report2

Server Activity

Server Activity reports lets you choose time interval that you are interested in and then shows nice activity report for that interval. This report gives you information about SQL Server and/or System CPU usage, Memory usage, Disk I/O usage, Network usage, SQL Server Waits and other activity:

MDW Server Activity report

You can actually click on the graph and get more information about what happened on the server at that time. I clicked on the “Disk I/O Usage” graph on the spike and got following report:

MDW - Server activity report 2

Behind the scene

It does not look like SQL Server 2008 February CTP6 has an interface to create new data collection sets. But BOL has a list of stored procedures that allows you to do just that. There is very little description on how to use these stored procedures, but I found that best way to understand how to create new data collection set is by scripting existing collection set. When you right mouse click on connection set, you have an option to “Script data collection set as”->”Create”.

Here are create scripts for each data set collection:

I was analyzing these scripts and found that there is a table msdb.dbo.syscollector_collector_types that contains a list of available collector types.  February CTP 6 was shipped with 4 collector types:

  • Generic T-SQL Query Collector Type
  • Generic SQL Trace Collector Type
  • Query Activity Collector Type
  • Performance Counters Collector Type

Each of these collectors is associated with 2 SSIS packages: collection package and upload package. I found these packages in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install. For example in this folder there is package TSQLQueryCollect.dtsx and package TSQLQueryUpload.dtsx. I opened and try to analyze these packages, but at this point that was just too much for me.

Why did I started to look behind the scenes? Because I wanted to make sure that it is possible to create new collection sets and add new collector types. And now I am very sure that we can. At this point I strongly believe that what we see in CTP 6 it is just a beginning. And in the not so distant future we will see Microsoft or the 3rd party releasing new collectors and collection sets. Microsoft did a good job finally setting framework for SQL Server monitoring software.

Analysis Services and MDW

SQL Server 2008 February CTP does not have any specific collectors and collection sets to collect information about Analysis Services. But it is just a matter of time before Microsoft or 3rd party will create them – there is definitely a demand for the Analysis Services monitoring software and MDW clearly allows adding new collectors.

In the near future I am planning to test:

  • Can I use “Generic T-SQL Query Collector Type” to execute OpenQuery or Linked Server query to Analysis Services 2008 DMVs and get same SSAS performance data that way?
  • Can I use “Generic SQL Trace Collector Type” to trace Analysis Services events?
  • Can I use “Performance Counters Collector Type” to get Analysis Services related performance data? At this point I believe that I am most likely to succeed for this item.

I will post my findings in my blog.

Note: For this post I used Snagit software to do screen shots and it was so much easier than doing this my old way: capturing screen (ALT+Prt Scr), then cutting out part of the screen in the Microsoft Paint and then changing size. I am very happy that I got this software.