Torsten Schuessler - Category: SQL Server

New Version of SQL Server Maintenance Solution by Ola Hallengren Now Available

Torsten Schuessler — Tue, 04 Jun 2013 09:41:00 +0000

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

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

Discrepancy by estimated TEMPDB space for CHECKDB and CHECKALLOC - WITH ESTIMATEONlY

Torsten Schuessler — Fri, 17 May 2013 09:39:00 +0000

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:

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

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

SQL Server 2012 Tempdb File Size Change tracked with Extended Events

Torsten Schuessler — Thu, 02 May 2013 11:51:00 +0000

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:

I wish you a nice day,
tosc

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

SQL Server Information

Torsten Schuessler — Wed, 20 Feb 2013 13:13:00 +0000

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 Information <--
SET nocount ON;goUSE [master];goSELECT
    CONVERT(CHAR(100), Serverproperty('MachineName')) AS 'MACHINE NAME',
    CONVERT(CHAR(50), Serverproperty('ServerName')) AS 'SQL SERVER NAME',

        (CASE WHEN CONVERT(CHAR(50), Serverproperty('InstanceName')) IS NULL
                THEN 'Default Instance'
              ELSE CONVERT(CHAR(50), Serverproperty('InstanceName'))
         END) AS 'INSTANCE NAME',

        CONVERT(CHAR(30), Serverproperty('EDITION')) AS EDITION,
        CONVERT(CHAR(30), Serverproperty('ProductVersion')) AS 'PRODUCT VERSION',
        CONVERT(CHAR(30), Serverproperty('ProductLevel')) AS 'PRODUCT LEVEL',

        (CASE WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 1
                THEN 'Clustered'
              WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 0
                THEN 'NOT Clustered'
              ELSE 'INVALID INPUT/ERROR'
         END) AS 'FAILOVER CLUSTERED',

        (CASE WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 1
                THEN 'Integrated Security '
              WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 0
                THEN 'SQL Server Security '
              ELSE 'INVALID INPUT/ERROR'
         END) AS 'SECURITY',

        (CASE WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 1
                THEN 'Single User'
              WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 0
                THEN 'Multi User'
              ELSE 'INVALID INPUT/ERROR'
         END) AS 'USER MODE',

        CONVERT(CHAR(30), Serverproperty('COLLATION')) AS COLLATION,
    Getdate() AS RunTime,
    @@SPID AS 'ID',
    SYSTEM_USER AS 'Login Name?',
    USER AS 'User Name';go
-- END SQL Server Information --

sql_server_informations.sql

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

SQL Server 2012 Service Pack 1(SP1) Customer Technology Preview 4 (CTP4) Available

Torsten Schuessler — Fri, 21 Sep 2012 05:58:00 +0000

MICROSOFT® SQL SERVER® 2012 SERVICE PACK 1 (SP1), COMMUNITY TECHNOLOGY PREVIEW 4 (CTP4)

Download:
SQL Server 2012 SP1 CTP4
SQL Server 2012 SP1 CTP4 Express
SQL Server 2012 SP1 CTP4 Feature Pack

The SQL Server 2012 builds that were released after SQL Server 2012 was released:
Download: http://support.microsoft.com/kb/2692828/

Full story »

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

Antivirus Software on SQL Server

Torsten Schuessler — Thu, 20 Sep 2012 14:37:00 +0000

It’s an old story, but still present - Why or why not installing antivirus software on a database server?

First of all, I’ve been on both sides of the debate with respect, but I can only, only partially agree to install antivirus software on a database server - e.g Microsoft SQL Server. And this only partially agreement means, first not to install antivirus software on SQL Server as many IT departments do in a standard procedure. In addition, the default settings are left untouched, so that every virtually byte is examined in real time.

Have you ever examined the amount of memory, high CPU spikes or high CPU usage and last but not least your high consumer of I/O activities on your (busy) SQL Server?
So let us take a closer look by using Process Explorer to identify antivirus and SQL Server tasks since start-up.

Identifying the SQL Server and Antivirus consumers

I prefer using Process Explorer to take a immediate first look what happens on the server, he includes many interesting counters for regarding some performance issues and more. By selecting View-Select Columns…, we can add interesting counters such as Private Bytes, Working Set, Threads, Handles, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes, CPU Time – as shown in the screenshot below.

The screenshot above shows that while the ntrtscan.exe (Real-time Scan Service) has read 70% of the bytes of SQL Server, it has performed 27 times as much read I/O as the read activity isn’t as efficient as SQL Server. And the tmlisten.exe (antivirus communication service) has only write 7% of the bytes of SQL Server, but it has performed four times as much write I/O as the write activity of SQL Server.

Now I want to see the leaders of high I/O Reads on my server by filtering I/O Reads (see screenshot below).

The first three consumers of I/O Reads are:

Antivirus (ntrtscan) with 111.641.100 Reads
SQL Server (sqlservr) with 4.622.225 Reads
Antivirus (tmlisten) with 2409809 Reads

Same doing on I/O Writes:

Antivirus (tmlisten) with 31.124.046 Writes
SQL Server (sqlservr) with 9.785.283 Writes
ReportingServices with 1.715.489 Writes

So, it’s possible for antivirus to consume many times of I/O’s of SQL Server which can really hamper performance on your (busy) SQL Server. It is possible that the antivirus is consuming more than 50% of total I/O activities! (Please note the recommendations to Microsoft SQL Server Database Engine Input/Output Requirements)

And what does Microsoft say for antivirus on your SQL Server (KB309422)?

"We strongly recommend that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, we recommend that before you roll out any virus-protection project, you test the whole system under a full load to measure any changes in stability and performance.
Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine whether there is any performance effect on the computer that is running SQL Server."

In my opinion it is very carefully worded, with a “It depends on …” – but they help us!

Read this KB very carefully with you Antivirus Administrator, talk with him about virus risks on the server, performance effects on a (busy) SQL Server and how intrusive the antivirus is at the ratio of the security risk level. In addition read also KB822158, there are further recommendations for the windows server and should be applied as well and first!

I don’t want to repeat these KBs, but here are some recommended must reads:

Turn off scanning of Windows Update or Automatic Update related files
Turn off scanning of Windows Security files
Turn off scanning of Group Policy related files
Directories and file-name extensions to exclude from virus scanning
Processes to exclude from virus scanning
Considerations for clustering

AND - consider an external virus scan against your SQL Server, which is possible in most enterprise antivirus products – often you needn’t a real time virus scan, it belongs to the security risk level!

I wish you a nice day,
tosc

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

SQL Server 2012 - New and Modified Dynamic Management Views and Functions - DMVs

Torsten Schuessler — Thu, 13 Sep 2012 13:36:00 +0000

That's just about the limit. In Books Online for SQL Server 2012 (BOL) by reading What's New (Database Engine), I came around the topic Manageability Enhancements (Database Engine) and its paragraph New and Modified Dynamic Management Views and Functions, and I only see six added or modified system views. And I think by myself, these are the same six DMVs I had read in CTP Denali BOL - no update?

But I knew that there are more new DMVs. I do not mean the AlwaysOn Availability Groups Dynamic Management Views and Functions of the AlwaysOn feature etc. ... - So, I think by myself - figure out!

OK, I have a SQL Server 2008 R2 instance and a SQL Server 2012 instance (both are only for testing, no production environment!). But I don't want to query each instance and copy the result for further investigations in excel - like this way:

USE [master];
GO
SELECT name, type, type_desc
FROM sys.system_objects WITH (NOLOCK)
WHERE name LIKE 'dm_%'
ORDER BY name OPTION (RECOMPILE);
GO

New Dynamic Management Views and Functions in SQL Server 2012

I create a valid linked server point form SQL Server 2012 to SQL Server 2008 R2 (@server = N'SQL2008R2'), then I run below query from SQL Server 2012 - which will find DMVs that did not exists in 2008 R2 ('SQL2008R2') - comparing the datasets in sys.system_objects.

USE [master]; --- execute on SQL Server 2012
GO
SELECT
N'sys.' + d.[name] AS [DMV NAME],
d.[type] AS [DMV TYPE],
d.type_desc AS [DMV TYPE DESC]
FROM
  [master].sys.system_objects AS d WITH (NOLOCK)
LEFT OUTER JOIN --- LinkedServer SQL Server 2008 R2
[SQL2008R2].[master].sys.system_objects AS r WITH (NOLOCK)
ON
d.[name] = r.[name] ---* collate Latin1_General_CI_AS
AND d.[schema_id] = r.[schema_id]
WHERE
  r.[name] IS NULL
  AND d.[schema_id] = 4
  AND d.[is_ms_shipped] = 1
  AND d.[name] like 'dm_%'
ORDER BY
  d.[name] OPTION (RECOMPILE);
GO

Linked Query Result

I get this resultset, and YES SQL Server 2012 has really 36 new Dynamic Management Views and Functions.


DMV NAME	DMV TYPE	DMV TYPE DESC
sys.dm_db_database_page_allocations	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_fts_index_physical_stats	V	VIEW
sys.dm_db_log_space_usage	V	VIEW
sys.dm_db_objects_disabled_on_compatibility_level_change	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_uncontained_entities	V	VIEW
sys.dm_exec_describe_first_result_set	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set_for_object	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_filestream_non_transacted_handles	V	VIEW
sys.dm_fts_index_keywords_by_property	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_semantic_similarity_population	V	VIEW
sys.dm_hadr_auto_page_repair	V	VIEW
sys.dm_hadr_availability_group_states	V	VIEW
sys.dm_hadr_availability_replica_cluster_nodes	V	VIEW
sys.dm_hadr_availability_replica_cluster_states	V	VIEW
sys.dm_hadr_availability_replica_states	V	VIEW
sys.dm_hadr_cluster	V	VIEW
sys.dm_hadr_cluster_members	V	VIEW
sys.dm_hadr_cluster_networks	V	VIEW
sys.dm_hadr_database_replica_cluster_states	V	VIEW
sys.dm_hadr_database_replica_states	V	VIEW
sys.dm_hadr_instance_node_map	V	VIEW
sys.dm_hadr_name_id_map	V	VIEW
sys.dm_logconsumer_cachebufferrefs	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_privatecachebuffers	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_consumers	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_hashentries	V	VIEW
sys.dm_logpool_sharedcachebuffers	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_stats	V	VIEW
sys.dm_logpoolmgr_freepools	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_respoolsize	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_stats	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_os_cluster_properties	V	VIEW
sys.dm_os_memory_broker_clerks	V	VIEW
sys.dm_os_server_diagnostics_log_configurations	V	VIEW
sys.dm_resource_governor_resource_pool_affinity	V	VIEW
sys.dm_tcp_listener_states	V	VIEW

... Now you can query Books Online for SQL Server 2012 ....

To struggle through these new Dynamic Management Views and Functions in SQL Server 2012, will be an ongoing and hard work :-)

I wish you a nice day,
tosc

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

New Version of SQL Server Maintenance Solution by Ola Hallengren Now Available

Torsten Schuessler — Mon, 10 Sep 2012 08:50:00 +0000

New Version Available

A new version of the SQL Server Maintenance Solution is available. You can now perform striped backups across multiple drives in the DatabaseBackup stored procedure. Simply specify multiple directories in the @Directory parameter; for example:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup, D:\Backup, E:\Backup, F:\Backup', @BackupType = 'FULL', @Compress = 'Y', @NumberOfFiles = 4

The new version also supports checking the consistency of a database on the file group level and on the table level. This capability is useful when you have VLDBs.

You can read more about the most recent version of the solution at http://ola.hallengren.com/versions.html or download it at http://ola.hallengren.com/scripts/MaintenanceSolution.sql

And now it's up to YOU!

2012 SQL Server Pro Community Choice Awards

The voting for the 2012 SQL Server Pro Community Choice Awards is now open! This year, the Ola Hallengren SQL Server Maintenance Solution has been nominated in the Best Backup & Recovery Product and Best Free Tool categories. So let us VOTE!!!

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

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

Service Pack 2 (SP2) is available for SQL Server 2008 R2

Torsten Schuessler — Fri, 27 Jul 2012 05:47:00 +0000

SQL Server R2 Service Pack 2 (SP2 Build 10.50.4000.0) is available:

http://www.microsoft.com/en-us/download/details.aspx?id=30437

List of the bugs that are fixed in SQL Server 2008 R2 Service Pack 2
http://support.microsoft.com/kb/2630458

I wish you a nice day,
tosc

"Try not to become a man of success but rather to become a man of value."
Albert Einstein

SQL Server 2012 Service Pack 1(SP1) Customer Technology Preview 3 (CTP3) Available

Torsten Schuessler — Wed, 25 Jul 2012 05:59:00 +0000

MICROSOFT® SQL SERVER® 2012 SERVICE PACK 1 (SP1), COMMUNITY TECHNOLOGY PREVIEW 3 (CTP3)

Download: http://www.microsoft.com/en-us/download/details.aspx?id=30375

The SQL Server 2012 builds that were released after SQL Server 2012 was released:

Download: http://support.microsoft.com/kb/2692828/

Full story »

"Try not to become a man of success but rather to become a man of value."
Albert Einstein