Category: Databases

SQL Server: Cumulative IO By Database since Last Restart

Here’s a TSQL script to get cumulative IO by database since last restart.

WITH Agg_IO_Stats
AS
(
  SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 / 1024.
         AS DECIMAL(12, 2)) AS io_in_gb
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
  GROUP BY database_id
),
Rank_IO_Stats
AS
(
  SELECT
    ROW_NUMBER() OVER(ORDER BY io_in_gb DESC) AS row_num,
    database_name,
    io_in_gb,
    CAST(io_in_gb / SUM(io_in_gb) OVER() * 100
         AS DECIMAL(5, 2)) AS pct
  FROM Agg_IO_Stats
)
SELECT R1.row_num, R1.database_name, R1.io_in_gb, R1.pct,
  SUM(R2.pct) AS run_pct
FROM Rank_IO_Stats AS R1
  JOIN Rank_IO_Stats AS R2
    ON R2.row_num <= R1.row_num
GROUP BY R1.row_num, R1.database_name, R1.io_in_gb, R1.pct
ORDER BY R1.row_num;

Source

SQL Database IO Consumption

Here’s a query I found that helps show database IO consumption.

 SELECT
        DB_NAME(DB_ID('DATABASENAME')) AS [Database Name]
      , mf.physical_name
      , io_stall_read_ms
      , num_of_reads
      , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
      , io_stall_write_ms
      , num_of_writes
      , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
      , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
      , num_of_reads + num_of_writes AS [total_io]
      , CAST((io_stall_read_ms + io_stall_write_ms)
        / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
    FROM
        sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf
    ON  fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]
    ORDER BY
            [Database Name] ASC, mf.physical_name
--  num_of_reads DESC
--  num_of_writes DESC
    OPTION (RECOMPILE) ;

SQL Server Optimization Rules of Thumb

This helped me today in troubleshooting and understanding some issues.
The performance counter SQL Server Access Methods: Full Scans/sec is very helpful in determining if you have a query doing full scans however, remember that it is for the entire database server not just a particular database.

Optimization Rules of Thumb

  • Always look at the query plan first. It will show you the optimal current execution plan from the query engine’s point of view. Find the most expensive part of the execution plan and start optimizing from there. However, even before that, make sure that the statistics on all tables in your query are up to date, by running the update statistics <TableName> command on all tables in your query.
  • If you see table scan, optimize. Table scan is the slowest possible way of execution. Table scan means not only that no index is used, but that there is no clustered index for this table at all. Even if you can only replace table scan with clustered index scan, it is still worth it.
  • If you see clustered index scan, find out whether it can be replaced with index seek. For that, find what conditions are applied to this table. Usually, conditions exist for two or three fields of the table. Find out the most selective condition (that is, the condition that would produce the smallest number of records if applied alone), and see whether an index on this field exists. Any index that lists this field first will qualify. If there is no such index, create it and see whether the query engine picks it up.
  • If the query engine is not picking up the existing index (that is, if it is still doing a clustered index scan), check the output list. It is possible that seek on your index is faster than clustered index scan, but involves bookmark lookup that makes the combined cost greater than use of a clustered index. Clustered index operations (scan or seek) never need bookmark lookup, since a clustered index already contains all the data. If the output list is not big, add those fields to the index, and see whether the query engine picks it up. Please remember that the combined size is more important than the number of fields. Adding three integer fields to the index is less expensive than adding one varchar field with an average data length of 20.Summarizing this rule, try to make your index covering, and see whether it works better than clustered index scan. Please note that it is not always possible to make the query engine pick up your index automatically. A small table or a low-selectivity index will produce clustered index scan, even if your index is covering.
  • If you see bookmark lookup, it means that your index is not covering. Try to make it covering if it makes sense (see the preceding guidelines).
  • The execution plan selected by the query engine may be not the best one. The query engine makes certain assumptions about disk subsystem and CPU cost versus IO cost. These assumptions sometimes can be incorrect. If you don’t believe that the query engine’s selection is the best one, run a query in the loop for 10 to 15 minutes with automatic selection, change the query to use your index (you will have to use index hint to force it), and then run it for 10 to 15 minutes again. Compare the results to see which one works better.
  • Avoid any operations on the fields, where possible. Some operations will prevent the use of the index on this field even if it exists—for example, the infamous ltrim(rtrim(FieldName)); other operations will degrade the performance. For example, instead of using the condition cast(DateField as varchar(20)) = @dateString, try to convert @dateString to an expression of datetime type first, and then compare it to DateField.
  • Please note that the query engine cost estimate does not include the cost of embedded procedure or function calls. If you compare between plain join and select from table-value functions, the latter would seem to have smaller cost, but it usually does not. In such a situation, use your own metrics to find out which query performs better.
  • When it is not possible to avoid operation on the field, use an index built on that expression. This can be done in two ways:
    • Create a calculated field based on your expression.
    • Create a view, and build an index on it.
Note    SQL Server requires certain conditions to be met in order to allow the use of calculated fields and indexed views (set quoted_identifier on, set arithabort on, and so on).
  • Indexed views are a good way to further speed up the query if you are not satisfied with the results. Indexed view is a clustered index built over the view’s select list. You can also define additional indexes for the indexed view, just as you can for any regular table. Indexed views take disk space and involve some maintenance overhead (every time underlying tables change, the indexed view also has to change), but they usually provide a good boost in performance, even after all other optimization techniques are exhausted.

Special thanks to Andrei Volkov for many interesting discussions about the SQL Server internals.
Source:  http://msdn.microsoft.com/en-us/library/aa964133(v=SQL.90).aspx

Defrag SQL MDF, LDF Files on Disk using Contig

Wrote this batch script to search an entire system (all disks) and find MDF/LDFs and then prompt whether or not to defrag. You need to have ‘contig’ from SysInternals. Also, set MINFRAGMENTS variable to something suitable for you.

Again, I wrote this quickly and YMMV certainly.

Code

@echo off
: Rich Kreider
:
: You need contig.exe from sysinternals: http://live.sysinternals.com/contig.exe
SETLOCAL
set MINFRAGMENTS=5
set CONTIGBIN=c:tempcontig.exe
if not exist %CONTIGBIN% goto missingcontig
: Don't play with this...
set TOTALMDF=0
set TOTALLDF=0
set FRAGCOUNT=0
FOR %%P IN (C D E F G H I J K L M N O P Q R S T U V W X Y Z) DO (
IF EXIST %%P:nul (
call :checkdrive %%P
)
)
: set __NeedsDefrag
for /f %%x in ('set __NeedsDefrag 2^>NUL ^|find /v /c ~~~') do (
if %%x geq 1 (
echo *** FOUND FILES NEEDIN DEFRAGMENTATION ***
call :ask
) else (
echo *** NO FILES NEED DEFRAGGED (MINIMUM FRAGS REQ: %MINFRAGMENTS%^) ***
)
)
goto eof
:checkdrive
for %%y in (mdf ldf) do (
echo ===Searching %%P:*.%%y...
for /F delims=, %%x in ('dir /b /s %1:*.%%y 2^>NUL') do (
if exist %%~fsx (
if %%y==mdf set /A TOTALMDF+=1
if %%y==ldf set /A TOTALLDF+=1
call :analyze %%~fsx
)
)
)
echo ===%1 MDF: %TOTALMDF% LDF: %TOTALLDF%
goto :eof
:analyze
for /F tokens=2,5 %%i in ('%CONTIGBIN% -a %1 ^| find is in') do (
if %%j geq %MINFRAGMENTS% (
set __NeedsDefrag^|%1^=%%j frags/file
)
)
goto :eof
:ask
set answer=
set /p answer=Defragment all? (y/n):
if %answer%== goto ask
if /i %answer:~0,1%==y goto defrag
if /i %answer:~0,1%==n goto eof
goto :eof
:defrag
for /f tokens=2* delims=|= %%A in ('set __NeedsDefrag') do (
echo Starting defrag on %%A...
%CONTIGBIN% -q %%A 2>NUL 1>NUL
for /F tokens=2 delims=: %%r in ('%CONTIGBIN% -a %%A ^| find Average') do (
echo Completed defrag on %%A (Was %%B now %%r^)
)
echo.
)
goto :eof
:missingcontig
cls
echo.
echo.
echo. Missing contig binary; you NEED this.
echo. I can start the download now, it should open
echo. in your default browser.
echo.
echo. After you download it, note the location
echo. and modify this file and set CONTIGBIN accordingly.
echo.
start http://live.sysinternals.com/contig.exe
goto eof
:eof
echo Press any key to quit...
pause >NUL

SQL 2008 Backups: Compression Comparison and Notes

width=180
Here’s a transact-SQL to show you a comparison of a compressed and uncomopressed backup from your SQL server instance.
Backup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time.
(Reference)

SELECT
  b.database_name 'Database Name',
  CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',
  CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',
  CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
  CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)'
FROM
  msdb.dbo.backupset b
WHERE
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
  AND b.backup_size > 0
ORDER BY
  b.backup_finish_date DESC

You can also find out compression ratio with this transact-SQL code (Reference):

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Determine if you have backup compression enabled on a database:

USE AdventureWorks2012 ;
GO
SELECT value
FROM sys.configurations
WHERE name = 'backup compression default' ;
GO

Configure backup compression and enable it on a database:

USE AdventureWorks2012;
GO
EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE ;
GO

Alternatively, you can use the WITH NO_COMPRESSION or WITH COMPRESSION in your BACKUP statement to override the database default.