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:

DBCC OPENTRAN — or DBCC OPENTRAN(‘tempdb’)

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:

DBCC INPUTBUFFER(<number>)

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:

Troubleshoot Sql Server Performance Bottlenecks

Pingback from:http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

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
FROM
(
SELECT
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
FROM (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE ‘%<SystemHealth>%’) AS t
) AS t
ORDER BY record_id desc

Monitor  Expensive Queries:

SELECT TOP 20
LEFT(P.CACHEOBJTYPE + ‘(‘ +P.OBJTYPE + ‘)’, 35) AS cacheobjtype,
stat.
p.usecounts,
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,
stat.total_physical_reads,
stat.total_logical_writes,
stat.total_logical_reads,
LEFT (CASE
WHEN PA.VALUE=32767 THEN ‘ResourceDb’
ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname, pa.value))
END, 40) AS dbname,
sql.objectid,
CONVERT (NVARCHAR(50), CASE
WHEN sql.objectid IS NULL THEN NULL
ELSE REPLACE (REPLACE (sql.[text], CHAR(13), ‘ ‘), CHAR(10), ”)
END) as PROCNAME,
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,
QPLAN.Query_Plan
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
OR
SELECT * FROM sys.dm_exec_requests ORDER BY CPU_TIME desc
OR
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
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

Link:http://www.ssas-info.com/VidasMatelisBlog/55_sql-server-2008-management-data-warehouse

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:

 

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:

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

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:

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

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:

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:

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:

• Create Disk collection set
• Create Query collection set
• Create Server collection set

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.