SQL hardware configuration

As a DBA as much as knowledge of query tuning and indexing and maintenance is important, so is important the other aspect i.e. Disk I/O configuration. Because sometimes how much ever you tune the query the problem lies elsewhere.

Raid 0: should generally be never used for SQL server
Raid 1: Mirrors data: provides protection from loss of data
Raid 5: stripping with Parity
Raid 6:stripping with double distributed Parity
Raid 10: stripped pair of mirrors
Raid 01: mirrorred pair of strips

For read-only data raid 5 or 6 are good

For heavy writes or OLTPs raid 1+0 is a good choice though expensive

Raid 1 is a good choice for transaction log which are mostly written to continuously

tempdb since is mostly writes Raid 1 or raid 1+0 is good option. Or else the RAM or any other specialized hardware is too.
Direct attached storage (DAS): disk are attached/built into server chasis and hence dedicated to each server. Easy assembly as well as performace troubleshooting. No support for failover clustering, disk array snapshots, cross data center or array based replication.

Storage Area networks (SAN):  Shared storage usage btween multiple servers. Ensures maximum use of storage space.
Since the sytem is complex, performance troubleshooting becomes difficult

Memory Management

This is excerpt from memory management chapter of book Troubleshooting SQL Server, A guide for accidental DBA (Some notes I put together from the chapter for self help: This chapter in the book is a wonderful read.)

No memory leak- SQL Server keeps all the memory allocated to it, whether it uses it or not. It does not return memory if the memory is not in use. So basically it looks like there is a memory leak. PS: SQL server will not release this memory unless the OS sets memory low resource notification flag, which indicates SQL server to release its memory allocations. While the memory high feature lets SQL Server know that it can grow to use additional memory. (A dedicated thread was introduced in SQL 2005 to monitor these memory notifications)

SQL      64 bit                                          SQL  32bit

Windows 2008                       OS limit 32 GB                                    limit  4GB --(enabling AWE helps SQL use additional memory unto 64gb)

SQL 2000 enterprise or SQL 2005 standard and above only has AWE enabling feature. SQL 2000 standard edition and below don't have this feature. (PS*: Remember, If the RAM or physical memory is less than 32GB then accordingly the SQL will have an upper limit which is less that the physical memory available. The above examples are based on RAM being around 64gb)

SQL Server 32 bit:

• Memory allocation: 2GB to User Mode VAS and 2GB to Kernel Mode VAS though Windows assigns it a complete of 4GB to operate. Out of 2GB User mode: most of it is buffer pool memory, and some is non-buffer pool memory.


• Buffer pool memory: SQL server calls VirtualAlloc function in WinAPI to allocate memory to itself. (VirtaulAlloc returns 32bit pointer which limits amount of usermode VAS to 2GB) Buffer pool memory allocations are for data pages and execution plans. This memory is pageable by Windows.


• Non-buffer pool memory allocations: Thread stack allocations, heap allocations, exteded stored procs, SQLCLR, linked servers, backup buffers. SQL Server calculates this MemToLeave which is: MaxWorkerThreads * 0.5MB + default revervation (256MB) where MaxWorkerThreads = (ProcessorCount-4)+256  (generally comes to about 0.4GB) Generally buffer pool will not require more memory than this, but incase this memory becomes Fragmented and there is not enough contingeous memory available, the amount of VAS reserved can be increased using -g 256> startup parameter. In 64-bit SQL, VAS is 8TB, greater than allowable physical RAM on a windows server, so this issue does not exist.
  So for 2GB user mode VAS: 1.6Gb is buffer pool and 0.4 GB is non-buffer pool memory.


• To allow 32-bit SQL to use more buffer pool memory 2 options exist:
  1.VAS tuning (available for Windows server with 4GB memory-RAM) -use with extreme caution, since here you are taking away about 1GB of Kernel VAS and trying to allocate to User VAS. In most cases avoid!
  2. enabling AWE -Address Windowing Extensions (for greater than 4GB RAM).  AWE is used to extend Buffer pool User memory. It requires additional configuration of the OS to use Physical Address Extensions (PAE). When PAE is enabled, the 32 bit memory management pointer is exapanded to 36-bit allowing OS to address 64GB of RAM or RAM's upper limit. For applications to make use of this additional memory they must use AWE. Thus instead of calling VirtuallAlloc, now SQL Server calls AllocateUserPhysicalPages function in Windows to allocate memory. This is non-pageable  by Windows.
  To use AWE, PAE is enabled in Windows 2008 using BCDEdit /set from command prompt. Next, 'awe enabled' sp_configure option is set in SQL server and SQL Server service account must have Locak Pages in Memory user right (assigned using Windows Group Policy Tiil, gpedit.msc). Restart SQL server required after enabling this. 'max server memory' sp_configure option used to set max memory allowed for AWE eg: 4gb to (MAX RAM not recommended), any memory that we think is required by SQL server.Since this is non-pageable memory, Windows OS cannot get this locked memory back if it needs it, so be careful while assigning AWE memory limit.

SQL Server 64 bit:

• In 64-bit SQL, VAS is 8TB for Kernel mode and 8TB for user mode, greater than allowable physical RAM on a windows server. Hence, AWE enabled bit has no use in 64-bit SQL. But Lock Pages in Memory option still exists.


• Also, since there is so much memory, The MemtoLeave for non-buffer pool and -g startup parameter also has no significance in 64-bit. But you have to manually monitor to ensure that Memory/Available Mbytes (non-buffer pool memory) remains abover 300MB as we gradually increase value of max server memory.


• Configuring memory through Min Max server memory bits(via SSMS or sp_configure): Mim server memory is minimum size to which SQL server can shrink buffer pool when WIN memory is under pressure. Max server memory is how much maximum memory a buffer pool can use). Min Server memory should always be set much lower than max memory (If min and max are set to same(not recommended!), we are intentionally locking pages in memory which will limit how much space SQL can free up for windows when windows hits low memory)(More on how to lock pages in memory:http://msdn.microsoft.com/en-us/library/ms190730(v=sql.105).aspx)

Diagnosing Memory pressure: low memory allocated to SQL means more pages to get from disk, means more physical IO and poor performance. Results in continuous buffer pool flushing called buffer pool churn.

SQL Server: Buffer manager\Page Life Expectancy: (time in seconds that a page exists in cache): This counter must be monitored over long periods of time in order to properly identify normal trends and away from normal value.

Free List Stalls/sec > 0 frequently, indicates memory pressure. If free pages counter is near to or is 0 and PLE drops at the same time means memory pressure.

Find and drop unused indexes across multiple servers

So we have a system with about 300+ dbs across 20 servers and all of the databases are identical. Each db represents a client.

/*
This query is used to find index, seeks, scans, lookups and updates across all tables in all  dbs on all servers.
criteria for unused indexes is seeks+scans+lookups<10
the index should not be unique or clustered
and table type is not a heap.

Run the below mentioned query across all servers using registered servers. It uses EXEC sp_msforeachdb to run query on each db
*/
CREATE TABLE #UnusedIndexes
(dbName sysname,
tablename sysname,
indexname sysname,
seeks BIGINT,
scans BIGINT,
lookups BIGINT,

updates BIGINT,
lastseek DATETIME,
lastscan DATETIME,
TotalSeeksScansLookups BIGINT,
SizeInKB BIGINT)


EXEC sp_msforeachdb N' USE [?]

INSERT INTO #UnusedIndexes
SELECT  DB_NAME(),o.name ,
i.name,
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates,
u.last_user_seek,
u.last_user_scan,
u.user_seeks+
u.user_scans+
u.user_lookups TotalSeeksScansLookups,
reserved_page_count * 8
FROM    sys.indexes i
JOIN sys.objects o ON i.object_id = o.OBJECT_ID
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
LEFT JOIN sys.dm_db_partition_stats ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id

WHERE   o.type <> ''S''
--and isnull(u.user_updates, 0) > 0
and i.type_desc <> ''HEAP''
AND ISNULL(u.user_seeks,0)+
ISNULL(u.user_scans,0)+
ISNULL(u.user_lookups,0) < 10
AND is_unique = 0
AND i.type_desc = ''NONCLUSTERED''
--  AND o.name = '''' AND i.name=''<indexname>''
AND DB_NAME() LIKE ''%_<type>''
;

'

SELECT * FROM #UnusedIndexes

DROP TABLE #UnusedIndexes

Once you get the output, put it in excel or a sql table for further queries

----determine the total number of databases in the system with similar schemas (in ours its  --301 dbs)
SELECT *
FROM sys.databases WHERE name LIKE '%%_<type>' AND
name NOT IN ('master','tempdb','model','msdb','MMDB','pubs','Northwind','mmdb','MMShare')--301  dbs

/*
Here, I have imported the data to a table called UnusedIndexes and ran the count query below:
--wherever the noofoccurences match the total number of stage dbs that is 301 for this run
AND the user seeks, scans and lookups are 0 or very minimal/negligible as compared to updated
means none of the stage database is using this INDEX
Hence the index can be deleted across all  dbs.

Incase of user seeks,scans,lookups and updates being null, does not give us much information..so we will have to go through each of the index usage and figure out whether to keep or delete it.
*/
SELECT tablename,indexname,COUNT(indexname) AS noofoccurences,SUM(totalSeeksScansLookups) as totalSeeksScansLookups,
SUM(updates) AS Totalupdates,sum(sizeinKB)/1000/1000 AS sizesavingsacrossAllinGB
from [dbo].[UnusedIndexes]
GROUP BY tablename, indexname
HAVING COUNT(indexname) >300
ORDER BY sum(sizeinKB) desc
/* As you begin deleting the indexes you might see a significant decrease in db size. This will also help the CPU since those many lesser indexes need to be updated and maintained.

After the above query run keep changing the --HAVING COUNT(indexname) >275 (and see how many more indexes it gives)


Sometimes an index is picked up by optimizer by mistake and hence shows up as used in a few databases. If that is the behaviour you see, those indexes can be dropped as well, unless you put that index in specifically for a large database or for a particular query.
*/

Be very careful dropping any index. Double check to see its not unique or clustered. We saw savings of approx total 50GB across all servers.

msdb cleanup

Recently I had to implement a job to remove msdb mail history and backup history.
We do daily build emails and full and differential backups. Though this does not increase the size of msdb to great extent it might grow considerably over a few months or year or so.

For mail cleanup, I got a tip from the following article:
http://www.mssqltips.com/sqlservertip/1732/sql-server-database-mail-cleanup-procedures/


1) To delete mail history.
We needed to keep upto 2 weeks of old data,
But not delete more than 3 months of data at a time, since that might block up msdb.

DECLARE @MinSentDate DateTime
select @MinSentDate=MIN(sent_date) from sysmail_allitems
DECLARE @DeleteBeforeDate DateTime
Set @DeleteBeforeDate = (Select DATEADD(mm,3, @MinSentDate))
if(dateadd(DD,-14,getdate())<@DeleteBeforeDate)
Begin
set @DeleteBeforeDate=dateadd(DD,-14,getdate());
End
Print @DeleteBeforeDate
--To delete messages with any/all sent status
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='sent'
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='unsent'
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='retrying'
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='failed'
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate
Print 'Mail history deleted before:'
Print @DeleteBeforeDate


2)To delete backup history
We do full backups every sunday and differential backups for the rest of the week.
Hence it was necessary to delete backup before sunday two weeks ago, since here again we wanted to keep two weeks worth of data.
Also, we did not want to delete more than 3 months of data at a time.


DECLARE @TodayDayOfWeek INT
DECLARE @StartOfPrevWeek DateTime
DECLARE @deletedate DateTime
SET DATEFIRST 1

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the sunday of the previous week
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+7), GetDate())
--since Sunday is full backup day, we choose saturday
SET @deletedate = @StartOfPrevWeek-1

DECLARE @minbkdate DATETIME;
SET @minbkdate = (SELECT MIN(backup_start_date)
FROM backupset WITH (nolock));
print @minbkdate
SET @minbkdate = (SELECT dateadd(mm,3,@minbkdate));


IF (@deletedate < @minbkdate )
BEGIN
SET @minbkdate = @deletedate;
END;

Exec sp_delete_backuphistory @minbkdate
Print 'Backup history deleted before:'
PRINT @minbkdate


These 2 steps were put in a job and scheduled to run weekly.