SQL Server - How much resource is a SQL database using - memory, disk, IOPS,CPU

How much resource is each SQL database using?. There are different types of resource on the database server. Memory , disk,  IOPS, CPU.

The main difficulty with defining the resource usage for a specific database is the wide impact of one query across multiple databases. For example a query with a SORT may spill into the TempDB.

Other examples , are joins and transactions across more than one database

 These links to post showing techniques to isolate different resource usage. Analysing the resource usage from different angles , should give you a rough idea of usage .  Use this information to make decisions such as – add another database ? or move a database ? or add more resource to the server.

 If you’re  tuning – tuning at the database level can be frustrating – which is why it’s more effective at the query level .

 Memory 

 SQL Server – Calculate Database memory usage with sys.dm_os_buffer_descriptors  

Database Size – extra disk space

SQL Server Database Size and Free Disk Space ... - SQL Server

IOPS 

Measure current read & writes . Do the server drives have capacity?

 SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

CPU Utilization

 This one is difficult – I’ve achieved the best results  using the DMV - sys.dm_exec_query_stats . Read more on SQL Server – Find high impact queries with sys.dm_exec_query_stats  

 

 

SQL Server - BobMgrGetBuf Sort Big Output Buffer write not complete after 60 seconds

Sometimes you may see this message in the SQL Server Error Log files

 

 BobMgrGetBuf Sort Big Output Buffer write not complete after 60 seconds

 

It’s associated with IO subsystem issues , other common errors appearing and related are:

 

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

 

Read more on  IO Requests taking longer than 15 seconds to - SQL Server DBA

The message relates to large SORTS , probably the TempDB. Bob is the Big Output Buffer. Writres are heavy sequential and occur  on transit tables .During the heavy sequential writes, it is possible the disk IO cannot maintain the resource requirements.

 There are two options :

1)Reduce the workload

2)Add resources

Reducing the workload could mean : a) make queries more efficient and can be achieved through analysing the query Execution Plan, b)  review index usage. Both options  can be quick wins without much justification required in spending terms.

Justifying adding more resource such as  disk resource can be trickier – disk can be expensive but including management of the disk can increase costs.

 

Other options:

1)       If relevant – use Lock Pages in Memory

2)       Work with network \ storage administrators to identify the bottleneck . To identify what is delaying the IO. The bottleneck can occur across the whole environment – such as SVC, network switches and Disk arrays.

Read More

TempDB performance and strategy checklist - SQL Server DBA

IO Requests taking longer than 15 seconds to - SQL Server DBA

 Powershell,PSEexec and lock pages in memory – made easy

SQL Server – Troubleshoot DBCC SHRINKDATABASE slow performance

When observing  a DBCC DHRINKDATABASE on the DMV sys.dm_exec_requests  command column , you’ll notice different commands appearing.

Step 1 – DbccSpaceReclaim   - Prepares for data moves by cleaning up space –e.g deferred allocations and manage empty extents

Step 2 – DbccFileCompact - Adjusting pages beyond the target to before the target.

Step 3 – DbccLOBCompact - LOB data compact

 Sometimes it may appear the command is taking a long time. Some reasons may be:

1) If a heap is moved all Non Clustered index physical link references to a record number on a data page need to be updated. If there are large NonClustered Indexes , the Query Processor  needs to process all the updates.

2)  Analyse for other processes blocking the lock required to move the page SQL Server – Current queries executing on SQL Server

3)  Analyse IO subsystem

Read More

SQL Server – How to Predict the DBCC SHRINKDATABASE finish time

SQL Server - Monitoring a Rollback and sys.dm_exec_requests ...

SQL Server Storage and IO performance - SQL Server DBA

SQL Server - How to check Disk Latency

Question: I have a SQL Server 2008 that has data , log  and tempdb files placed in separate RAID 10 disks on SAN. In one data drive, which has a single database file (200 GB) there is relatively high avg. disk sec/ read (ranging 100 ms - 900 ms). The SAN system is  optimized for high read rates , according to the storage guys

Do you have any advice on how  reduce the avg. disk sec/read for this drive?.

Answer:  I’m assuming you’re referring to the Physical Disk Counter.  Therefore the avg. disk sec/read stat is captured at the Partition Manager level. In  the Windows Storage Stack , the Partition Manager sits  below the Volume Manager. This means , the delay of 100 ms – 900 ms is the time the IO leaves the Partition Manager and returns.

The challenge is to identify where the delay is occurring below the Partition Manager. Quite often it’s difficult to pinpoint which queue can be creating the delay. For example, there are hardware device driver queues . You may need to speak to your System Administrators to assist in troubleshooting queues

From a DBA perspective , a initial method implement is to monitor the disk queue counters and the DMV - sys.dm_db_index_operational_stats. For a broader performance testing with Perfmon read Performance Monitor Counters for SQL Server performance testing

The sys.dm_db_index_operational_stats  DMV returns  IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert, update, and delete. Note: the stats are refreshed after every SQL Server Service restart

Email me at jack@sqlserver-dba.com , for any questions or feedback

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Read More

IO_completion on disk solved with Wait Stats and Perfmon - SQL ...

SQL Server - Disk IO performance and SQLIO

SQL Server Storage and IO performance

 

SQL Server – Moving a Non Clustered Index to another filegroup

Moving a Non Clustered Index to another filegroup is straightforward. Sometimes a DBA may decide after careful analysis that  excessive contention exists on the disk drives and the solution may be to separate out the Non Clustered Indexes with the data. When data is looked up on a Non Clustered Index , the index is checked first and then related data is retrieved. The theory is if the data and index are on separate disks or LUNs , IO  response times may improve , which leads to improved performance.

In an optimally configured system the disk drives tend to be the slowest part of the transactions. Not always! But mainly. There are various perfmon counters to check. Read Performance Monitor Counters for SQL Server performance testing

 

USE jvTest
GO
-- Create the FG1 filegroup on the jvTest 
--It's a new filegroup on separate drive
ALTER DATABASE jvTest
ADD FILEGROUP FG1;
GO
-- Adds the FG1dat file >>  FG1 filegroup. 
--replace the filename with your own 

ALTER DATABASE jvTest
ADD FILE 
(
    NAME = FG1dat,
    FILENAME = 'E:\MSSQLSERVER\MSSQL$INST1\Data\TranFG1dat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 1000MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP FG1;
GO
--Assumption:IX_MyIndex on Table_1 exists in the PRIMARY filegroup
USE [jvtest]
GO
CREATE NONCLUSTERED INDEX [IX_MyIndex] ON [dbo].[Table_1] 
(
	[myval] ASC
)WITH (DROP_EXISTING = ON) ON [FG1]
GO

 Read More

Window Perfmon scripting, SQL Server perfmon and how to perfmon

SQL Server non-clustered index checklist - SQL Server DBA

SQL Server - List all indexes of all tables