Tuesday, April 24, 2012

SQL Server buffer pool usage


All data is stored as 8 KB pages on the disk. When a readrequest is made for a page then that page is cached in SQL Server buffer poolso it can be reused for consequent requests for same page.
So all the database objects will consume some of buffer pooland it is good idea to keep track of what is using the buffer pool and how muchof it.

Every page cached in buffer pool is assigned a descriptorwhich uniquely identifies the cached data page.

sys.dm_os_buffer_descriptors will return all cached pages for alldatabases including user and system databases. Please note that pagesassociated with Resource database are also included. Following query will showhow much biffer cache is consumed by individual user databases

SELECT
DB_NAME(database_id) AS [Database Name],
(COUNT(*) *8/1024.0)/1024 AS [Buffer Cache Size (GB)],
(COUNT(*) *8/1024.0) AS [Buffer Cache Size (MB)]
FROM
sys.dm_os_buffer_descriptors
WHERE
database_id >4 -- filter system databases
ANDdatabase_id <> 32767 -- filter Resource database
GROUP BY
DB_NAME(database_id)
ORDER BY
[Buffer Cache Size(MB)] DESC;

Here is the output

Database Name
Buffer Cache Size (GB)
Buffer Cache Size (MB)
MyTestDB1
26.34036255
26972.53125
MyTestDB2
13.73649597
14066.17188
MyTestDB3
11.53723145
11814.125
MyTestDB4
10.90490723
11166.625
MyTestDB5
10.03324127
10274.03906
MyTestDB6
7.539787292
7720.742187
MyTestDB7
6.231697082
6381.257812
MyTestDB8
4.891433716
5008.828125
MyTestDB9
1.46887207
1504.125
MyTestDB10
0.601066589
615.492187


Also it is good idea to know what objects within a databaseare using how much of the SQL buffer.


SELECT top 10
OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
p.index_id ,
COUNT(*) / 128 AS [Buffer Cache size(MB)] ,
COUNT(*) AS [Buffer Pagecount]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors
ASb ON a.allocation_unit_id= b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id= DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id] ,
p.index_id
ORDER BY[Buffer Page count] DESC ;


ObjectName
index_id
Buffer Cache size(MB)
Buffer Page count
tbl_test1
3
4947
633216
tbl_test2
1
3087
395184
tbl_test3
1
2066
264574
tbl_test4
1
610
78152
tbl_test5
1
437
55956
tbl_test6
1
242
31055
tbl_test7
1
208
26706
tbl_test8
1
57
7309
tbl_test9
49
5
669
tbl_test10
1
3
490

Why restore is taking longer than back and what is going on with restore?


As a DBA there are numerous times when we restore databases. While doing so I have asked these questions to myself several times. Here I will try to answer them as best as I can

So why my database backup takes 1 hour and restore takes 8 hours?

There are multiple reasons for this

  1. Let’s say I am backing up a database with total size 500 GB but actual data is 100 GB (With 400 GB allocated space available). Backup will have to be only for 100 GB. However while restoring entire 500 GB database has to be rebuilt so restore time is much more than backup time.
  2. Other reason could be that you are not using “Instant File Initialization” in which deleted data on the disk is replaced by zeros while data and log files are being created. So if your data file total size is 500 GB with 100 GB actual data then after writing 100 GB data all other bytes will be filled with zeros (instant file Initialization not enabled). This will add significant time to restore.


Now to the second question; what is going on with restore?

Usually you can specify STATS while restoring and restore command will output the status every N %

E.g: Here I am restoring TestDB with STATS set to 10 (every 10% display message)

RESTORE DATABASE [TestDB] FROM  DISK = N'D:\TestDB\TestDB_BK.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Here is what you will see in message tab of SSMS

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 292888 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 6 pages for database 'TestDB', file 'TestDB_log' on file 1.
RESTORE DATABASE successfully processed 292894 pages in 23.265 seconds (98.355 MB/sec).

Along with this there are entries logged in SQL error log which also tell you what is happening while restore is going on (Run sp_readerrorlog to read the SQL Error logs)

2012-04-24 13:44:51.190 spid64       Starting up database 'TestDB'.
2012-04-24 13:44:51.230 spid64       The database 'TestDB' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-04-24 13:44:51.640 spid64       Starting up database 'TestDB'.
2012-04-24 13:44:52.150 Backup       Restore is complete on database 'TestDB'.  The database is now available.
2012-04-24 13:44:52.160 Backup       Database was restored: Database: TestDB, creation date(time): 2012/04/24(13:27:09), first LSN: 1417:196:183, last LSN: 1417:289:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\TestDB\TestDB_BK.bak'}). Informational message. No us

But this does not tell you much about exactly what is going on while restoring

So there are some undocumented trace flags which can be used to find out exact status of restore.

Trace flag 3004: This flag turns on information regarding instant file initialization. Enabling this trace flag will not make this information available to view.

Trace flag 3605: This flag will send some DBCC output to the error log. This trace flag needs to be enabled to see the instant file initialization information made available by trace flag 3004.

Now let’s restore with these 2 trace flags turned on

DBCC TRACEON(3004, 3605,-1)

RESTORE DATABASE [TestDB] FROM  DISK = N'D:\TestDB\TestDB_BK.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 292888 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 6 pages for database 'TestDB', file 'TestDB_log' on file 1.
RESTORE DATABASE successfully processed 292894 pages in 23.265 seconds (98.355 MB/sec).

From SQL Serve error log you can see there are lot of other messages logged related to the restore which are quite helpful.

2012-04-24 13:45:25.240 spid64       RestoreDatabase: Database TestDB
2012-04-24 13:45:25.240 spid64       Opening backup set
2012-04-24 13:45:25.260 spid64       Restore: Configuration section loaded
2012-04-24 13:45:25.260 spid64       Restore: Backup set is open
2012-04-24 13:45:25.260 spid64       Restore: Planning begins
2012-04-24 13:45:25.270 spid64       Halting FullText crawls on database TestDB
2012-04-24 13:45:25.270 spid64       Dismounting FullText catalogs
2012-04-24 13:45:25.270 spid64       X-locking database: TestDB
2012-04-24 13:45:25.270 spid64       Restore: Planning complete
2012-04-24 13:45:25.270 spid64       Restore: BeginRestore (offline) on TestDB
2012-04-24 13:45:25.300 spid64       Restore: PreparingContainers
2012-04-24 13:45:25.310 spid64       Restore: Containers are ready
2012-04-24 13:45:25.310 spid64       Zeroing E:\TransactionLogs\TestDB_log.ldf from page 1 to 128 (0x2000 to 0x100000)
2012-04-24 13:45:25.310 spid64       Zeroing completed on E:\TransactionLogs\TestDB_log.ldf
2012-04-24 13:45:25.310 spid64       Restore: Restoring backup set
2012-04-24 13:45:25.310 spid64       Restore: Transferring data to TestDB
2012-04-24 13:45:48.420 spid64       Restore: Waiting for log zero on TestDB
2012-04-24 13:45:48.430 spid64       Restore: LogZero complete
2012-04-24 13:45:48.580 spid64       FileHandleCache: 0 files opened. CacheSize: 12
2012-04-24 13:45:48.580 spid64       Restore: Data transfer complete on TestDB
2012-04-24 13:45:48.590 spid64       Restore: Backup set restored
2012-04-24 13:45:48.590 spid64       Starting up database 'TestDB'.
2012-04-24 13:45:48.640 spid64       The database 'TestDB' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-04-24 13:45:48.650 spid64       Restore-Redo begins on database TestDB
2012-04-24 13:45:49.140 spid64       Rollforward complete on database TestDB
2012-04-24 13:45:49.150 spid64       Restore: Done with fixups
2012-04-24 13:45:49.160 spid64       Restore: Transitioning database to ONLINE
2012-04-24 13:45:49.170 spid64       Restore: Restarting database for ONLINE
2012-04-24 13:45:49.200 spid64       Starting up database 'TestDB'.
2012-04-24 13:45:49.310 spid64       FixupLogTail(progress) zeroing E:\TransactionLogs\TestDB_log.ldf from 0xa2200 to 0xa4000.
2012-04-24 13:45:49.310 spid64       Zeroing E:\TransactionLogs\TestDB_log.ldf from page 82 to 94 (0xa4000 to 0xbc000)
2012-04-24 13:45:49.310 spid64       Zeroing completed on E:\TransactionLogs\TestDB_log.ldf
2012-04-24 13:45:49.420 spid64       PostRestoreContainerFixups: running fixups on TestDB
2012-04-24 13:45:49.440 spid64       PostRestoreContainerFixups: fixups complete
2012-04-24 13:45:49.450 spid64       PostRestoreReplicationFixup for TestDB starts
2012-04-24 13:45:49.640 spid64       PostRestoreReplicationFixup for TestDB complete
2012-04-24 13:45:49.640 spid64       Restore: Database is restarted
2012-04-24 13:45:49.640 Backup       Restore is complete on database 'TestDB'.  The database is now available.
2012-04-24 13:45:49.640 spid64       Resuming any halted fulltext crawls
2012-04-24 13:45:49.650 spid64       Restore: Writing history records
2012-04-24 13:45:49.650 Backup       Database was restored: Database: TestDB, creation date(time): 2012/04/24(13:27:09), first LSN: 1417:196:183, last LSN: 1417:289:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\TestDB\TestDB_BK.bak'}). Informational message. No us
2012-04-24 13:45:49.650 spid64       Writing backup history records
2012-04-24 13:45:49.670 spid64       Restore: Done with MSDB maintenance
2012-04-24 13:45:49.670 spid64       RestoreDatabase: Finished


Here are some of the other undocumented (or less documented) trace flag which come handy while checking backup & restore detailed status
Trace flag 3014: Also provides detailed information on steps during backup and restore.  
Trace Flag 3604: Sends DBCC output to screen instead of error log

Caution: These trace flags should be used only by professional DBAs since there are side effects of using undocumented trace flags

Friday, April 20, 2012

How to calculate IOPS per disk


There are some numbers already provided by vendors

SAS (15K RPM) à 175 IOPS
SATA (7.2K RPM) à 75 IOPS

Now how do they come up with these numbers?

The formula is

Estimated IOPS = 1 / ((Seek time/1000) + (Latency / 1000))

Now let’s put this formula into perspective

Seagate Cheetah 15k hard drive


Estimated IOPS = 1 / ((average read seek time + averagewrite seek time)/2)/1000 + (Average latency / 1000)

Estimated IOPS = 1 / ((3.65 / 1000) + (2.0 / 1000) = 1 /(0.00365) + (0.002) = 176.9911

Answer is ~ 175 IOPS

Wednesday, April 18, 2012

Create a SQL Server Database on network drive


Sometime back I came across a situation when a server ranout of local storage and among other things we did not have HBA card readilyavailable so SAN storage could not be attached.

Only solution was to map a network drive and move somedatabase files to this network drive. So here is a small article on how to create a database on network drive

For SQL Server 2008and SQL Server 2005

By default you can not have database files (MDF, NDF, LDF)on a network drive. This is because any network glitch can cause databasecorruption along with I/O issues inherently caused by network drives.

But it is “allowed” by tweaking a setting in SQL Servertrace flags.

  1. Enable trace flag 1807 (Description from BOL: Allows you to configure SQL Server with network-based database files.)
DBCC TRACEON(1807, -1)

  1. Now make sure account under which SQL Server service is running has full access to the network location

  2. Create database

    CREATE DATABASE [NetDriveDB] ON PRIMARY
( NAME = N'NetDriveDB',
FILENAME = N'\\computername\DBDrive\NetDriveDB_Data.mdf' ,
SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH= 1024KB )
LOG ON
( NAME = N'NetDriveDB_log', FILENAME = N'\\computername\DBDrive\NetDriveDB_Log.ldf' ,
SIZE = 2048KB , MAXSIZE = 4096GB , FILEGROWTH = 10%)
GO

For SQL Server 2008R2

In SQL Server 2008 R2 you do not need to turn on 1807 flag. However we need to make sure it is UNC path (\\ComputerName\SharedFolder\Resource\)

  1. Now make sure account under which SQL Server service is running has full access to the network location

  2. Create database

    CREATE DATABASE [NetDriveDB] ON PRIMARY
( NAME = N'NetDriveDB',
FILENAME = N'\\computername\DBDrive\NetDriveDB_Data.mdf' ,
SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH= 1024KB )
LOG ON
( NAME = N'NetDriveDB_log', FILENAME = N'\\computername\DBDrive\NetDriveDB_Log.ldf' ,
SIZE = 2048KB , MAXSIZE = 4096GB , FILEGROWTH = 10%)
GO

Tuesday, April 17, 2012

SQL Server 2000 & 7.0 Trace Flags


Here are some of the trace flagswhich are available for SQL server 2000 & 7.0

To turn on the trace

DBCC TRACEON(<Flag>, -1)

To check the trace status

DBCCTRACESTATUS(-1)

To turn off the trace

DBCC TRACEOFF(<Flag>, -1)

-1 in above is for applying tracesetting globally on the sever

Some of them are documented andsome of them are not

1
Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.
106
Disables line number information for syntax errors.
107
Interprets numbers with a decimal point as float instead of decimal.
205
Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat.
206
Provides backward compatibility for the setuser statement.
208
SET QUOTED IDENTIFIER ON.
242
Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.
243
The behavior of SQL Server is now more consistent because nullability checks are made at run time and a nullability violation results in the command terminating and the batch or transaction process continuing.
244
Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multirow UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables.
257
Will invoke a print algorithm on the XML output before returning it to make the XML result more readable.
260
Prints the versioning information about extended stored procedure dlls.
302
Prints information about whether the statistics page is used, the actual selectivity (if available), and what SQL Server estimated the physical and logical I/O would be for the indexes. Trace flag 302 should be used with trace flag 310 to show the actual join ordering.
310
Prints information about join order. Index selection information is also available in a more readable format using SET SHOWPLAN_ALL, as described in the SET statement.
325
Prints information about the cost of using a nonclustered index or a sort to process an ORDER BY clause.
326
Prints information about the estimated and actual cost of sorts.
330
Enables full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins.
506
Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.
652
Disables read ahead for the server.
653
Disables read ahead for the current connection.
809
Limits the amount of Lazy Write activity in SQL Server 2000.
1180
Forces allocation to use free pages for text or image data and maintain efficiency of storage.
1200
Prints lock information (the process ID and type of lock requested).
1204
Returns the type of lock participating in the deadlock and the current command affect by the deadlock.
1205
Returns more detailed information about the command being executed at the time of a deadlock.
1206
Used to complement flag 1204 by displaying other locks held by deadlock parties
1609
Turns on the unpacking and checking of remote procedure call (RPC) information in Open Data Services. Used only when applications depend on the old behavior.
1704
Prints information when a temporary table is created or dropped.
1807
Allows you to configure SQL Server with network-based database files.
2505
Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log.
2508
Disables parallel non-clustered index checking for DBCC CHECKTABLE.
2509
Used with DBCC CHECKTABLE.html to see the total count of ghost records in a table
2528
Disables parallel checking of objects by DBCC commands.
2701
Sets the @@ERROR system function to 50000 for RAISERROR messages with severity levels of 10 or less. When disabled, sets the @@ERROR system function to 0 for RAISERROR messages with severity levels of 10 or less.
3104
Causes SQL Server to bypass checking for free space.
3111
Cause LogMgr::ValidateBackedupBlock to be skipped during backup and restore operations.
3205
Disables hardware compression for tape drivers.
3222
Disables the read ahead that is used by the recovery operation during roll forward operations.
3502
Prints a message to the log at the start and end of each checkpoint.
3503
Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to read-only databases).
3602
Records all error and warning messages sent to the client.
3604
Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605
Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)
3607
Skips automatic recovery (at startup) for all databases.
3608
Skips automatic recovery (at startup) for all databases except the masterdatabase.
3609
Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.
3626
Turns on tracking of the CPU data for the sysprocesses table.
3640
Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.
4022
Bypasses automatically started procedures.
4030
Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.
4031
Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER.
4032
Traces the SQL commands coming in from the client. The output destination of the trace flag is controlled with the 3605/3604 trace flags.
7300
Retrieves extended information about any error you encounter when you execute a distributed query.
7501
Dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster than in earlier versions and no longer require unique indexes. This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.
7502
Disables the caching of cursor plans for extended stored procedures.
7505
Enables version 6.x handling of return codes when calling dbcursorfetchex and the resulting cursor position follows the end of the cursor result set.
7525
Reverts to the SQL Server 7.0 behavior of closing nonstatic cursors regardless of the SET CURSOR_CLOSE_ON_COMMIT state in SQL Server 2000.
8202
Replicates all UPDATE commands as DELETE/INSERT pairs at the publisher.
8206
Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers in SQL Server 2000.
8207
Enables singleton updates for Transactional Replication, released with SQL Server 2000 Service Pack 1.
8599
Allows you to use a savepoint within a distributed transaction.
8679
Prevents the SQL Server optimizer from using a Hash Match Team operator.
8687
Used to disable query parallelism.
8721
Dumps information into the error log when AutoStat has been run.
8783
Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled.
8816
Logs every two-digit year conversion to a four-digit year.

Ref: http://www.sqlservercentral.com/articles/Monitoring/traceflags/737/