My 24 hours of wait stats

Below is excellent feedback and links received from Paul Randal of SQLSkills on 24 hours of wait stats data captured from several of SQL Server instances l help manage, labeled for this post, instance1,instance2 etc.
I will update this as l go through the suggestions.

INSTANCE1
For the LATCH_EX, you’ll need to drill in with my sys.dm_os_latch_stats code at http://www.sqlskills.com/blogs/paul/advanced-performance-troubleshooting-waits-latches-spinlocks/. If you have CXPACKET waits, you’ll maybe see ACCESS_METHODS_DATASET_XX latches, which are normal for parallel scans.

For OLEDB, if the average wait times are very low, it’s likely to be something like a monitoring tool constantly running DMVs (that use OLE-DB under the covers), rather than linked-server queries. If they’re more than a 1-2 ms each, it’s likely not from perf monitoring tools running small DMV queries; it could be from linked-server queries (in which case you’ll need to debug the queries on the remote server), running DBCC CHECK* commands, SSIS from an OLEDB source, or your own DMV queries.

For the PAGEIOLATCH_SH/EX/UP, these are pages that are being read from disk. You want to figure out why SQL Server is needing to read pages and a pivot on that is why the buffer pool doesn’t contain all data required. I’d be concerned about the 10-25ms for each read.
Check out Module 5 of the Wait Stats Pluralsight course for more details.

Your BACKUPBUFFER/BACKUPIO waits are backup waiting for a buffer to use and reads to occur. These are for filling and writing the buffers used during a backup. Commonly you’ll see hundreds or thousands per data backup, with the cumulative wait for those occurring per backup being about the length of time the backup takes. Higher wait times per BACKUPIO would be from writing to a network file or maybe a tape file.

The ASYNC_IO_COMPLETION waits are a bit skewed and any long duration is from data backups. There are three waits per data backup that occurs, and one of these three is for the entire duration of the data-copy portion of the backup, which is what causes the high wait times. Although these look high, they are expected. See http://www.sqlskills.com/blogs/paul/cause-high-duration-async_io_completion-waits/.

The BACKUPTHREAD waits are where you have backup parallelism happening – from multiple data files on different drive letters/mount points or multiple backup files on different drive letters/mount points.

The PREEMPTIVE_OS_FILEOPS are from various operations around creating new files on NTFS.

The MSQL_XP (and any PREEMPTIVE_OS_GETPROCADDRESS) are extended stored procedures executing code in your DLLs. To reduce MSQL_XP, you’ll need to speed up your DLL code, although these waits could be because of third-party software using XPs.

CXPACKET is pretty high, although the wait times aren’t that bad. Check out Module 5 of the Wait Stats course on Pluralsight where I explain CXPACKET and some of the causes and fixes. Definitely make sure you’re expecting parallelism for the query plans that are doing it (using my waiting tasks script at http://www.sqlskills.com/blogs/paul/advanced-performance-troubleshooting-waits-latches-spinlocks/). You do have one of the classic patterns: CXPACKET + PAGEIOLATCH_SH, which could be parallel table scans occurring instead of more desirable index seeks.

Not sure why you’re seeing so many PREEMPTIVE_OS_CREATEFILE here. If you’re interested, take a look at the method at http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type/, set it up for that wait type and send me some of the results.

The WRITELOG look fine at only 1.2ms each. You might be able to reduce these by removing any unused or duplicate nonclustered indexes, and other things that cause extra log – if they exist. These could be just normal as WRITELOG is usually present in the top waits.
See Module 5 in the Wait Statistics Pluralsight course for more info, plus http://www.sqlperformance.com/2012/12/io-subsystem/trimming-t-log-fat and http://www.sqlperformance.com/2013/01/io-subsystem/trimming-more-transaction-log-fat.

INSTANCE2
The ASYNC_NETWORK_IO are <1ms each, so I'm not concerned. These are usually from poor application programming (doing RBAR - row by agonizing row - processing rather than caching the data client side and letting SQL Server progress) or possibly network delays. Check out Module 5 of the Wait Stats course on Pluralsight. INSTANCE3
The PREEMPTIVE_OS_WAITFORSINGLEOBJECT goes hand in hand with the ASYNC_NETWORK_IO.

The PREEMPTIVE_OS_FILEOPS are from various operations around creating new files on NTFS.

Not sure why you’re seeing so many PREEMPTIVE_OS_REPORTEVENT here. If you’re interested, take a look at the method at http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type/, set it up for that wait type and send me some of the results.

INSTANCE4 – SHAREPOINT1
The PREEMPTIVE_OS_AUTHENTICATIONOPS (and any similar security calls) are calls out to windows (and maybe through to an AD or domain controller) to gets security info. These look fine.

The LCK_M_U are update locks being acquired to (for example) allow a multi-row update to not block concurrent reads until all rows are ready to be updated. For long waits, you could dig in with the blocked process report to figure this out – see Michael Swart’s blog at http://michaeljswart.com/2011/05/when-to-use-blocked-processes-reports/.

The LCK_M_S are likely being blocked by something that has (or is waiting for) a SCH_M lock – an index rebuild, for instance – or an X/IX lock (maybe lock escalation). It could also be an application using the serializable isolation level (or DTC, which forces serializable, or using .Net TransactionScope which defaults to serializable.) You can use the blocked process report to figure this out – see Michael Swart’s blog at http://michaeljswart.com/2011/05/when-to-use-blocked-processes-reports/.

INSTANCE5 – SHAREPOINT2
IO_COMPLETION/WRITE_COMPLETION waits are I/Os that are not table/index pages, or not reading them for query purposes. WRITE_COMPLETION can be from writing to a database snapshot (e.g. while running DBCC CHECK*). I’ll be doing a blog post about these in the next couple of weeks. These don’t look problematic on your server.

For the LCK_M_X, I’d want to know why these are occurring – most likely some sort of lock escalation if they’re table locks. It could also be an application using the serializable isolation level (or DTC, which forces serializable, or using .Net TransactionScope which defaults to serializable.) You can use the blocked process report to figure this out – see Michael Swart’s blog at http://michaeljswart.com/2011/05/when-to-use-blocked-processes-reports/.

SQLTRACE_XXX can be ignored, as is so in the latest version of my code.

Nothing to add for the other servers.

Cheers


Paul S. Randal
CEO and Owner, SQLskills.com, SQL MVP, MS Regional Director

=================================================

Database Snapshot

Sample T-SQL Code for creating a database snapshot.

use AdventureWorks2012
go

sp_helpfile — used to get the logical database name needed for below

CREATE DATABASE AWSnapshot
ON (
NAME = ‘AdventureWorks2012_Data’,
FILENAME=’d:tlogAWSnapshot.ss’
)
AS SNAPSHOT OF AdventureWorks2012;

–using it
USE AWSnapshot;
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = 53;

–reverting from snapshot
RESTORE DATABASE AdventureWorks2012
FROM DATABASE_SNAPSHOT=’AWSnapshot’;

Database Mirroring

Prefered long term solution for this suggested by Microsoft is using AlwaysOn High Availablilty.

T-SQL Code for mirroring [note the port numbers have to be different.

/****** Object: Endpoint [mirroring] on Server1 Script Date: 4/27/2014 3:32:37 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7575, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

/****** Object: Endpoint [mirroring] on Server2 Script Date: 4/27/2014 3:31:17 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7595, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

/****** Object: Endpoint [mirroring] on Witness instance Script Date: 4/27/2014 3:30:20 AM ******/
CREATE ENDPOINT [mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7585, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

m1

m2

m3

m4

m5

m6

m7

m8

m9

On the Mirror Server, restore a backup with NO RECOVERY

USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N’D:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLBackupAdventureWorks2012.bak’
WITH FILE = 5,
MOVE N’AdventureWorks2012_Data’
TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDATAAdventureWorks2012_Data.mdf’,
MOVE N’AdventureWorks2012_Log’
TO N’D:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDataAdventureWorks2012_log.ldf’,
NORECOVERY,
NOUNLOAD,
STATS = 5

GO

Installing Microsoft SQL Server 2014

Below shows a step by step install of Microsoft SQL Server 2014 which was releases on April 1st 2014. in pictures with some notes.

Notes:

For a server install, Microsoft recommends using separate domain accounts for each sql service. Below setup is the same for a server environment install, but works best for a developer workstation. Also, for a stand alone sql server, you will want to place the mdf, ldf, backup files in separate sas,san, ssd, raid configured san setup uptimally for the individual files you are storing.

4-15-2014 9-19-13 PM

4-15-2014 9-19-41 PM

4-15-2014 9-20-16 PM

4-15-2014 9-20-44 PM

4-15-2014 9-21-34 PM

4-15-2014 9-22-08 PM

4-15-2014 9-23-22 PM

4-15-2014 9-24-23 PM

4-15-2014 9-25-16 PM

4-15-2014 9-25-31 PM

4-15-2014 9-26-19 PM

4-15-2014 9-27-05 PM

4-15-2014 9-27-45 PM

4-15-2014 9-28-17 PM 4-15-2014 9-29-00 PM

4-15-2014 9-29-50 PM 4-15-2014 9-30-05 PM

4-15-2014 9-30-38 PM

4-15-2014 9-44-55 PM

4-15-2014 9-52-23 PM

4-15-2014 9-18-29 PM

Below shows SQL Server Configuration Manager 2014.  Remember to enable TCP/IP and restart the SDE and SSA – SQL database and sql server agent engines.

 

4-15-2014 10-10-04 PM

 

4-15-2014 10-11-15 PM

 

 

 

Corporations Data Extract Download into Microsoft SQL Server

In this post l will be detailing the steps l used to ETL – Extract Transform and Load into a Microsoft SQL Server 2012 database from Washington State Corporations Data download. This showcases SQL Server inbuilt SSIS [SQL Server Integration Services] Data import and export wizard. We will be working with the text (tab limited) and not the XML version first.

1)

First download the text form data file from this link, click here.

Unzip the file as shown below.

p1

2)

Create a database bucket and re-size to accomodate a large data import and reasonable file growth by value instead of percentage which is less efficient.

create database CorporationsDataWA;

USE [master]
GO
ALTER DATABASE [CorporationsDataWA]
MODIFY FILE ( NAME = N’CorporationsDataWA’, SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [CorporationsDataWA]
MODIFY FILE ( NAME = N’CorporationsDataWA_log’, SIZE = 102400KB , FILEGROWTH = 102400KB )
GO

p2

p3

 

3)

Below show step by step screenshots of the ETL process. I also chose to save the SQL server generated workflow process for this ETL as a separate SSIS package for later review, and execution.

Notice the ETL error message generated. The default column width of 50 characters for the business name and address fields was not enough to handle the input data without truncation, l tried 1100 and finally settled for 200 characters for each of these name, address fields. And that worked. With a clear data dictionary one might have known beforehand the maximum characters for those data fields in the text limited input data file.

Arrows indicate important entry fields or steps. Click on images for a larger version.

3-29-2014 11-40-55 AM 3-29-2014 11-41-35 AM 3-29-2014 11-44-03 AM 3-29-2014 11-44-18 AM 3-29-2014 11-48-37 AM 3-29-2014 11-49-00 AM 3-29-2014 11-49-37 AM 3-29-2014 11-49-54 AM 3-29-2014 11-50-27 AM 3-29-2014 11-52-48 AM 3-29-2014 11-53-01 AM 3-29-2014 11-53-32 AM 3-29-2014 11-56-06 AM 3-29-2014 11-56-34 AM 3-29-2014 11-56-50 AM 3-29-2014 11-57-14 AM 3-29-2014 11-58-17 AM 3-29-2014 11-59-01 AM 3-29-2014 11-59-12 AM 3-29-2014 11-59-45 AM 3-29-2014 12-00-54 PM

 

B)

Importing Governing People (same process as above)

3-29-2014 12-35-53 PM 3-29-2014 12-36-30 PM 3-29-2014 12-36-42 PM 3-29-2014 12-36-52 PM 3-29-2014 12-37-03 PM 3-29-2014 12-37-59 PM 3-29-2014 12-38-14 PM 3-29-2014 12-38-32 PM 3-29-2014 12-38-54 PM 3-29-2014 12-39-20 PM 3-29-2014 12-39-32 PM 3-29-2014 12-40-03 PM 3-29-2014 12-40-48 PM 3-29-2014 12-41-03 PM

 

Below are screenshots showing one of the SSIS packages, with content(s) settings.

3-29-2014 1-39-29 PM 3-29-2014 1-43-33 PM 3-29-2014 1-44-15 PM 3-29-2014 1-45-04 PM 3-29-2014 1-45-21 PM 3-29-2014 1-45-47 PM 3-29-2014 1-46-16 PM

Powershell: Export Windows Server event logs to CSV

This powershell cmdlet  enables you to manage your windows server event logs, and it also enables you to get at the events found within those event logs.

Simple basic code example to do this.

Windows Server hostname is: SERVER1

Modify the logname as needed:  system, security, application etc.

get-eventlog -logname system -computername SERVER1 -after ‘1/1/2014’ | export-csv system.csv

Click here for the Microsoft TechNet reference web page on this.

Microsoft SQL Server 2014

This new release is slated for April 1st 2014.

“SQL Server 2014 is the culmination of thousands of hours of hard work from Microsoft engineers and thousands of hours of testing and input from our preview customers. The result is an important component of Microsoft’s overall cloud-first data platform. The platform delivers breakthrough performance, accelerated insights through tools everyone uses and the ability to scale globally on-premises and in the cloud – letting our customers get the most from their data. “
– source Microsoft Offical Blog