Some Practical Useful SQL Queries For SharePoint Adminstrator

In this article, I will share some very practical and useful SQL queries that a SharePoint administrator will require. You can use them as is, modify these as per your environment and combine multiple utilities into a single one to get a comprehensive output.

SQL Query to get details on Disk Utilization and IO

SQL Server performance depends heavily on physical memory. SQL Server constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. The log records need to be flushed to the disk before a transaction can be declared committed. SQL Server uses TempDB for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQL Server.

The sys.dm_io_virtual_file_stats DMV gives you details at the IO for both reads and writes as shown below. The sys.dm_io_virtual_file_stats DMV will show an IO stall when any wait occurs to access a physical data file. IO stalls are recorded at the file level and you can also obtain the IO Stalls at the database level directly out of the DMV. Run these on the WSS_Content database.

SELECT *

FROM sys.dm_io_virtual_file_stats (NULL,NULL)

ORDER BY io_stall_read_ms, io_stall_write_ms

By getting this information it is very easy to ORDER BY io_stall_read_ms, io_stall_write_ms, or by io_stall that is an accumulation of reads and writes.

 

Figure: Result of sys.dm_io_virtual_file_stats

You can get the database name by using the following query and get result as shown below.

select db_name(a.database_id) AS dbname,

b.name, a.file_id,

a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,

a.num_of_writes,

( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb

from sys.dm_io_virtual_file_stats(NULL,NULL) a

JOIN sys.master_files b ON a.file_id = b.file_id

AND a.database_id = b.database_id

ORDER BY a.io_stall DESC

 

 

Figure: SQL I/O Details

If you have a slow I/O subsystem then your users may experience performance problems such as slow response times, and tasks that abort due to timeouts. You can use the following performance counters to identify I/O bottlenecks. Note, these average values tend to be skewed (to the low side) if you have an infrequent collection interval. For example, it is hard to tell the nature of an I/O spike with 60-second snapshots. Also, you should not rely on one counter to determine a bottleneck; look for multiple counters to cross-check the validity of your findings.

Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded then more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.

Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. You can interpret this number as follows.

• Less than 10 ms: very good
• Between 10 - 20 ms: okay
• Between 20 - 50 ms: slow, needs attention
• Greater than 50 ms: Serious I/O bottleneck

Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. The guidelines are the same as above.

Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent then it represents an I/O bottleneck.

Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond the 85 percent capacity.

Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond the 85 percent capacity.

When using the preceding counters, you may need to adjust the values for RAID configurations using the following formulas:

• Raid 0:- I/Os per disk = (reads + writes) / number of disks
• Raid 1:- I/Os per disk = [reads + (2 * writes)] / 2
• Raid 5:- I/Os per disk = [reads + (4 * writes)] / number of disks
• Raid 10:- I/Os per disk = [reads + (2 * writes)] / number of disks

For example, you have a RAID-1 system with two physical disks with the following values of the counters.

Disk Reads/sec: 80
Disk Writes/sec: 70

Avg. Disk Queue Length: 5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 that indicates a border line I/O bottleneck.

SQL Query to get details of Index Fragmentation

sys.dm_db_index_physical_stats returns size and fragmentation information for the data and indexes of the specified table or view. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table then one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. It does not return information about xVelocity memory optimized columnstore indexes.

The following query returns all the indexes and their fragmentation statistics in the current database as shown below.

Run the following on the WSS_Content Database:

SELECT

OBJECT_NAME(A.[object_id]) as 'TableName',

B.[name] as 'IndexName',

A.[index_id],

A.[page_count],

A.[index_type_desc],

A.[avg_fragmentation_in_percent],

A.[fragment_count]

FROM

sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN

sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id

Order by A.[avg_fragmentation_in_percent] desc

 

 

Figure 6-3: Index Fragmentation

For interpreting the avg_fragmentation_in_percent value the following are the guidelines.



If you query sys.dm_db_index_physical_stats on a server instance that is hosting an AlwaysOn readable secondary replica then you might encounter a REDO blocking issue. This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

SQL Script to rebuild or reorganize indexes

The following script can be used to rebuild and reorganize indexes. In this example 30% fragmentation is an arbitrary decision point at which to switch between reorganizing and rebuilding. This can be changed as per your organization's decision. You will see the output as shown below: 

-- <<DB_NAME>> to be replaced with the Content Database name

USE <<DB_NAME>>

GO

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO 

 

 

Figure: Index Reorganize and Rebuild

SQL Query to get space occupied by each table

The following query gives an output of space used by each table in the database. You can run it against the Content Database tables to get the details of the sizes of tables to check for unusual growth of tables.

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),

reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),

unused VARCHAR(18))

 

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

 

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,

CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB

FROM #RowCountsAndSizes

ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

 

DROP TABLE #RowCountsAndSizes

This gives results as shown below:

 

Figure: Space Occupied by Tables

You can also use the following command to get more details as shown below.

EXECUTE sp_MSforeachtable

@precommand = 'CREATE TABLE ##Results

( name nvarchar(128),

rows char(11),

reserved varchar(50),

data varchar(50),

index_size varchar(50),

unused varchar(50)

)',

@command1 = 'UPDATE STATISTICS ?;',

@command2 = 'INSERT INTO ##Results EXECUTE sp_spaceused [?];',

 

@postcommand = 'SELECT * FROM ##Results order by unused desc; DROP TABLE ##Results'

Go  

 

 

Figure: Details of Tables Spaces: Occupied, Unused, Index Size

SQL Query to Get Number of Active Connections

The following query gets the number of active SQL Connections.

SELECT

hostname AS [Client],

program_name AS [Client Application],

DB_NAME(dbid) AS [Database Name],

COUNT(dbid) AS [Number of Connections],

UPPER(loginame) AS [Login or UserID]

FROM

sys.sysprocesses

WHERE

dbid > 0 AND DB_NAME(dbid) NOT IN ('master','model','msdb','tempdb')

GROUP BY

hostname,

program_name,

dbid,

loginame

ORDER BY

[Number of Connections] desc

You will see output as shown below :

 

Figure: Number of Active Connections

SQL Query to Get Virus Infected Documents

The following query gets the details of virus-infected documents in SharePoint.

SELECT

DirName,

LeafName,

VirusStatus,

VirusInfo,

VirusVendorID

FROM

AllDocs WITH (NOLOCK)

WHERE

(VirusStatus>0) and (VirusStatus IS NOT NULL)

SQL Query to get the memory breakdown of all cached compiled plans

You can use the dynamic management view, dm_exec_cached_plans, to find cached query plans, cached query text, the amount of memory taken by cached plans and the reuse count of the cached plans. The following query plans are taking up memory in the procedure cache. This may be wasted memory if we cache plans for queries that never get called again. This may be a good use case to Optimize for Ad Hoc or for Forced Parameterization.

SELECT

( CAST(COUNT(*) AS VARCHAR(10))

+ ' query plans are taking up memory in the procedure cache.') AS Details

FROM sys.dm_exec_cached_plans AS cp

WHERE cp.usecounts = 1

AND cp.objtype = 'Adhoc'

AND EXISTS (SELECT 1 FROM sys.configurations WHERE name = 'optimize for ad hoc

workloads' AND value_in_use = 0)

HAVING COUNT(*) > 1;

The following example returns a breakdown of the memory used by all compiled plans in the cache.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,

omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes

FROM sys.dm_exec_cached_plans AS ecp

JOIN sys.dm_os_memory_objects AS omo

ON ecp.memory_object_address = omo.memory_object_address

OR ecp.memory_object_address = omo.parent_address

WHERE cacheobjtype = 'Compiled Plan'

ORDER BY pages_allocated_count DESC

The output is as shown below:

Figure : Memory breakdown of all cached compiled plans

SQL Query to Get Backup Details

The following query gets backup details:

SELECT

d.[name] as DatabaseName,

' Last Backed Up: ' + COALESCE(cast(b.backup_finish_date AS varchar(10)) , 'Never') AS Finding

FROM master.sys.databases d

LEFT outer JOIN msdb.dbo.backupset b ON d.name = b.database_name

WHERE d.database_id <> 2

AND d.state <> 1 /* Not currently restoring, like log shipping databases */

AND d.is_in_standby = 0 /* Not a log shipping target database */

AND d.source_database_id IS NULL /* Excludes database snapshots */

GROUP BY d.name, b.backup_finish_date

You will get output as shown below:



Figure: Backup Information of Databases