Torsten Schuessler

Yesterday, I received the "Welcome as a Speaker for SQLSat 230 Rheinland" – Mail.

BANG!

I am touched ...

We are happy to announce the schedule for SQLSaturday #230 Rheinland and welcome you as one of our selected speakers.

What an honor! I will talk about performance and manageability of tempdb, from the perspective of a database administrator.

I am happy about, that there are 3 InsideSQL’s:

• Christoph Muthmann with his “Einsatzmöglichkeiten von Windows Functions”
• Andreas Wolter with “SQL Server under Attack – Angriffszenarien”
• and my humble self.

Before I forget, take a closer look at Pre-Conference SQLSaturday #230 on Friday– very cheap & interesting!

Are you a photographers? Please get in touch with Dirk Hondong!

CU at SQLSaturday #230 | Rheinland!
tosc 

Ola Hallengren and his ongoing work of his SQL Server Maintenance Solution - SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.The solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.

Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2011 and 2010 SQL Server Magazine Awards. I love this tool to perform index maintenance on instances with VLDBs - and the best it's free!

The new version supports:

• lock timeout settings in DatabaseIntegrityCheck and IndexOptimize
• support for indexes and statistics on objects that are created by internal SQL Server components
• ... and so on :-)

It can be uses like this:

EXECUTE dbo.IndexOptimize @Databases = 'AdventureWorks', @LockTimeout = 600

The new version has some performance optimizations and minor bug fixes

Download: http://ola.hallengren.com/scripts/MaintenanceSolution.sql

Please feel free to contact him if you have any questions. And I tell you, Ola will consider it!!!

I wish you a nice  day,
tosc

I came around this problem (depends on SQL Server 2008 R2 jutst to SQL Server 2012) by reading Paul S. Randal's and Ana's blog posts:

• How does DBCC CHECKDB WITH ESTIMATEONLY work (Paul)
• DBCC CHECKDB WITH ESTIMATEONLY: Do you trust it? (Ana)

DBCC CHECKDB runs CHECKALLOC, CHECKTABLE, CHECKCATALOG etc. against database, tables, views and so on. And I have learnded, however CHECKDB is executed against master database, a second CHECKDB is also running internally on the mssqlsystemressource.

So in my way, I want only make consistency check of disk space allocation by DBCC CHECKALLOC WITH ESTIMATEONLY against a ~ 200GB database, to know the estimated amount of tempdb space.

I run following DBCC command on SQL Server 2012 Enterprise Edition:

DBCC CHECKALLOC () WITH ESTIMATEONLY, TABLERESULTS;
GO

And I get this result

Estimated TEMPDB space (in KB) needed for CHECKALLOC on database XXX = 35383596.

OK, nothing special - but I thought about the posts of Ana and Paul, and a blog entry of Running SQP on SQL Server Blog - Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 & 2549

So next shot comparing CHECKDB and CHECKALLOC:

DBCC CHECKDB WITH ESTIMATEONLY, TABLERESULTS; 
GO

DBCC CHECKALLOC WITH ESTIMATEONLY, TABLERESULTS;
GO

And the results are diffrent!
For CHECKDB:

Estimated TEMPDB space (in KB) needed for CHECKDB on database XXX = 27165.

And for CHECKALLOC:

Estimated TEMPDB space (in KB) needed for CHECKALLOC on database XXX = 35383596.

This isn't an expected result - what a discrepancy!

By the way I have made a Microsoft Connect entry: http://connect.microsoft.com/SQLServer/feedback/details/787049/discrepancy-by-estimated-tempdb-space-for-checkdb-and-checkalloc-with-estimateonly

So feel free to vote.

I wish you a nice day,
tosc

We are using SQL Server 2012 and the tempdb is growing so fast. How do I effectively find what’s causing my tempdb growing. Can I monitor the file size change with Extended Events to find out the ...

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server. tempdb is re-created every time is started so that the system always starts with a clean copy of the database. The database engine uses tempdb for:

• Temporary user objects (temp tables, table variables ...)
• Internal objects (work tables/work files/ intermediate results ...)
• Version store for data modification transactions (Online indexes, After trigges, Snapshot, MARS ...)
• Service Broker, database mail ...

But this is another deep dive topic of tempdb latch contention on allocation pages... :-)
For now I only want to know

What's causing that my tempdb is growing so fast?

I usually look first to the tempdb file size and autogrow settings, you know something like this:

--> SQL Server tempdb file size and autogrow information <--
SET NOCOUNT ON;
GO  
USE [master];
GO
-- Check for trace flag 1117/1118 or any other trace flag
DBCC TRACESTATUS (1117) WITH NO_INFOMSGS; -- Grows all data files at once, else it goes in turns.
DBCC TRACESTATUS (1118) WITH NO_INFOMSGS; -- Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent
DBCC TRACESTATUS () WITH NO_INFOMSGS;

SELECT
   old.physical_name AS 'FileName',
   old.name AS 'LogicalName',
   old.size * 8.0 / 1024.0 AS 'OriginalSizeInMB',
   new.size * 8.0 / 1024.0 AS 'CurrentSizeInMB',
   'GrowthType' = 
       CASE
           WHEN new.growth = 0 THEN 'File is fixed size and will not grow.'
           WHEN new.growth > 0 AND new.is_percent_growth = 0 
               THEN 'Growth increment is in units of 8-KB pages, rounded to the nearest 64 KB'
           ELSE 'Growth increment is expressed as a whole number percentage.'
       END,
   'GrowthIncrement' =
       CASE
           WHEN new.is_percent_growth = 0 THEN CAST((new.growth * 8 / 1024) AS NVARCHAR(30)) + 'MB' 
           ELSE CAST (new.growth AS NVARCHAR(30)) + '%'
           END, 
   'MaximumFileSize' =
       CASE new.max_size -- file size, in 8-KB pages
           WHEN 0 THEN 'No growth is allowed.'
           WHEN -1 THEN 'File will grow until the disk is full.'
           -- !Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file!
           WHEN 268435456 THEN 'Log file will grow to a maximum size of 2 TB.' 
           ELSE CAST (new.max_size * 8.0 / 1024.0 AS NVARCHAR(30)) + 'MB'
       END
FROM
   MASTER.sys.master_files old INNER JOIN tempdb.sys.database_files new ON
   old.FILE_ID = new.FILE_ID
WHERE
   database_id = DB_ID('tempdb')
   AND old.size <> new.size

In fact, this is a good starting point to presize tempdb, for peak periods or set autogrow to be rare but "big enough", but I want to know what's causing my tempdb to grow so fast.

So I came out on Extended Events. Extended Events are a new mechanism introduced in SQL Server 2008 to capture SQL Server performance data with low overhead compared to SQL Server profiler, server side tracing, DBCC commands and other utilities. The scope of Extended Events is far beyond this tip and please refer to the articles below for better understanding of this feature. Extended Events allow more granular level tracking that was impossible in prior SQL Server versions. Extended Events in SQL Server 2012, XE in short, allows you capturing performance monitoring data at individual database level or per session level and for this post I will track information at the database level for tempdb.

The code snippet below creates the session to track tempdb file size changed and some informations of client application, session id, client hostname and so on.

--Drop the event if it already exists
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'tempdb_file_size_changed')
    DROP EVENT SESSION [tempdb_file_size_changed] ON SERVER;
GO

-- Create event
CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION
   (sqlserver.client_app_name,sqlserver.client_hostname, sqlserver.database_id,sqlserver.session_id,
   sqlserver.session_nt_username,sqlserver.username) WHERE ([database_id]=(2))) 
ADD TARGET package0.event_file
(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\tempdb_file_size_changed.xel')
WITH (MAX_DISPATCH_LATENCY=1 SECONDS)
GO

Once the script is completed and started I can wait for my tempdb rumbling.

To summarize the file size change, I ran these following queries:

SET NOCOUNT ON;
GO  
USE [master];
GO
SELECT
       database_name AS DatabaseName
       , file_type AS FileType
       , SUM (size_change_kb) AS Total_Size_Change_KB
       , SUM (duration) AS Total_Duration
       , client_app_name AS Client_Application
       , session_id AS SessionID
       
FROM (
       SELECT
           n.value ('(data[@name="size_change_kb"]/value)[1]', 'int') AS size_change_kb
           , n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
           , n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
           , n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
           , n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
           , n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
           
       FROM 
           (   SELECT CAST(event_data AS XML) AS event_data
               FROM sys.fn_xe_file_target_read_file(
                   N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\tempdb_file_size_changed*.xel',
                   NULL,
                   NULL,
                   NULL)
           ) AS tab
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
GROUP BY client_app_name, database_name, file_type, session_id
ORDER BY client_app_name

And what was causing my tempdb, it was me by Microsoft SQL Server Management Studio :-( - only for this blog entry.

Feel free to download these scripts:

• autogrow_settings_tempdb.sql
• tempdb_file_size_extended_events.sql
• tempdb_xml_event_data.sql

I wish you a nice  day,
tosc

Die Frankfurter Datenbanktage finden im Frühjahr 2013 vom 14. bis 15. März statt. An zwei Konferenztagen werden Datenbankthemen aus der Welt von IBM, Oracle, Microsoft und Open Source-Datenbanken in fünf parallelen Vortragstracks und rund 60 intensiven Sessions behandelt.

Am Vortag der Konferenz, am 13. März gibt es einen optional buchbaren Schulungstag zu „Virtualisierung, Cloud Computing und Hochverfügbarkeit mit SQL Server“. Maic Beher und Uwe Ricken zeigen die technischen Möglichkeiten, mit Hilfe von HyperV, SCOM und SQL Server komplexe Cloud-Lösungen zu realisieren. AlwaysOn, Mirroring und Replikation sind weitere Themen des Hands on Trainings.

Ruprecht Dröge zeigt in seiner Session einen Praxisvergleich der Hochverfügbarkeitslösungen mit MS SQL Server. Er ist ist Autor der Bücher „SQL Server 2012″, „SQL Server 2008″ und „SQL Server 2005“, die bei Microsoft Press erschienen sind. Lars Platzdasch geht auf Internals zur IO-Infrastrukturplanung von MS SQL Server-Umgebungen ein: IO-Diagnose, IO-Stresstests, Tools zur Problemerkennung und SAN-Optimierung. „SQL Server 2012 Express - Möglichkeiten und Grenzen“ ist das Thema von Robert Panther. Der mehrfach zertifizierte Datenbank-Experte, Trainer und Buchautor veröffentlichte im Mai 2012 bei Microsoft Press in der Reihe "Richtig einsteigen" eines der ersten deutschsprachigen Bücher zu SQL Server 2012. Anfang 2010 erschien bei entwickler.press sein SQL Server Performance-Ratgeber. Daniel Caesar, Autor des Buches "SQL Server 2008 R2 Einführung für Administratoren und Entwickler", das 2010 bei Galileo erschienen ist, spricht über MS SQL Server 2012: Er wird neue Funktionen des MS SQL Server 2012 aus Sicht einer SharePoint 2010 Plattform vorstellen und bewerten.

Mitglieder der PASS Deutschland e.V. erhalten die Möglichkeit, einen Rabatt von 20% auf alle Teilnehmerpakete zu nutzen!

 Natürlich gibt es auch die Möglichkeit die PASS vor Ort kennen zu lernen. Christoph Muthmann(MVP) und Torsten Schüßler beantworten gerne Eure Fragen - wir freuen uns auf Euch!

Only a short script to get informations about your SQL Server enviroment, like machine name, instance name, edition, version, level, cluster security, user mode, collation and login. I use it as a piece on my whole admin scripts.

sql_server_informations.sql