How to troubleshoot Writelog wait type?
Few weeks ago when I was teaching the SQL Server PTO Workshop, I didn’t do a deep dive into how to troubleshoot writelog wait type. Before I wrote something, I decided to look what was out there. I think Sakthivel does a far more justice to the issue then I would have. Please have a read, how T-Log works and why you might see writelog wait type.
- Sakthivel Chidambaram; What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server? (Link)
- SQLCAT, Lindsey Allan; Diagnosing Transaction Log Performance Issues and Limits of the Log Manager (Link)
- SQLCAT Blog; Deploying SQL Server 2005 with SAN #3 (Link)
- CSS SQL Server Engineer Blog; Discussion About SQL Server I/O (Link)
- MSDN, SQL Server 2005 Technical Articles; SQL Server I/O Basics, Chapter 2 (Link)
- Bob Dorr, Microsoft SQL Server Principal PFE; Microsoft SQL Server Database Engine I/O PowerPoint Presentation (Link)
(copied links from Sakthivel’s Blog).
Replication Configuration Issue
Client Question
Replication setup keeps failing, permissions are correct. Why does it keep giving me error code 5 (access denied).
Error Description
Command attempted:
\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\WOI_WorkOrde180608a5_10125.pre
(Transaction sequence number: 0x0000005F0001433C00CC00000000, Command ID: 10128)Error messages:
The process could not read file ‘\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\20111114154553\WOI_WorkOrde180608a5_10125.pre’ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21022)
Get help: http://help/MSSQL_REPL21022Access is denied.
(Source: MSSQL_REPL, Error number: MSSQL_REPL5)
Get help: http://help/MSSQL_REPL5
The error message is fairly clear; OS Error 5 is error messages returned from Windows API when it is trying to read from the file. But why was he getting this error? All permissions are set; SQL Server (Publisher) is able to write to it, why wouldn’t the SQL Server (Subscriber) be able to read it if it is running under same service account?
- Replication Configuration Issue – Setup Screen
If you take note of the image the location the file is written to; its \\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\..; problem with this is the R$ share. This is an administrative share, you must grant the SQL Server service account local administrative permissions on your Distributor to be able to read the file from there; which is against security best-practices. Therefore a better way to do is to use UNC paths.
- Create a new folder on you Distribution Server.
- Grant your SQL Server Service Account(s), if you are using more than one, permissions to read/write to the folder.
- Create a HIDDEN share on the folder (any share with $ after words is hidden share; I generally think anything that doesn’t need to be public on network; hide it).
- Control the permission on the share also; so only SQL Server Service Account can read/write to it.
- Re-setup your Replication using \\ServerName\ShareName$\.
Client confirmed it worked for them 
Last updated date for statistics?
There is many ways to get his information; however in most cases you get information for a single table. If you want to get it for all statistics in a database, how to do it? We can use the STATS_DATE function to get this information. Here is a simple query …
SELECT OBJECT_NAME(s.OBJECT_ID) AS TableName
, s.name AS StatisticsName
, STATS_DATE(s.OBJECT_ID,stats_id) AS LastUpdated
FROM sys.stats s
JOIN sys.tables t ON s.OBJECT_ID = t.OBJECT_ID
You can use this query to control what stats to build based on whent hey were last built. With the change windows being small in some organization, you might not be able to rebuild all stats every week. Specially for larger tables with large sample size; in which case basing your builds on Last Updated date will be helpful.
Getting List of Fragmented Indexes in a Database
Index fragmentation is naturally happens as an action of insert, deletes and updates. It is important to manage this fragmentation, if not you can have performance issue with range type queries.
SELECT OBJECT_NAME(IPS.OBJECT_ID) AS TableName
, CASE WHEN i.name IS NULL THEN
'HEAP TABLE, NO INDEXES'
ELSE
i.name
END AS IndexName
, index_type_desc AS IndexType
, index_depth
, avg_fragmentation_in_percent
, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') IPS
JOIN sys.indexes I ON IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
ORDER BY avg_fragmentation_in_percent
Adventures in Installing the SQL Server AdventureWorks Sample Database
I was thinking of writing an article about Microsoft SQL Server T-SQL Basics; however, in order to do the examples and demos I wanted to use a database that’s readily available on the Internet, so I decided to use the common AdventureWorks sample databaseavailable on Codeplex, After downloading the database, it should have been an easy install. I’ve installed it hundreds of times with no issues, but this time it didn’t work! Here’s my journey to figuring out a solution, hoping it will help some of you caught in similar circumstances.
After I agreed to the EULA, the installation abruptly stopped and I didn’t pay attention to error message. Maybe I should have, but then again if I did I wouldn’t have written this post. Anyhow, without trying to figure it out I decided to create the database manually by using the SQLCMD utility.
The AdventureWorks2008R2_SR1.exe file is self-extracting compressed file. I extracted the full contents of this file and got the following:
I didn’t know the AdventureWorks database gets created from scratch every time. I remember in the SQL 2005 days it was just MDF/LDF files. If you go into any of these folders you’ll see a myriad of files, including CSV, PNG, Visio Diagrams, etc..
The instawdb.sql file caught my eye. If you open it talks about using SQLCMD to create the database. So without any further delay I opened the command prompt and executed the following command from the directory where I had all these files extracted:.
sqlcmd -iinstawdb.sql -v SqlSamplesSourceDataPath="C:\Work\Tools\Sample Databases\AdventureWorks2008R2_SR1\" SqlSamplesDatabasePath="C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
It didn’t work. An error came back stating that Filestream was disabled. Okay, no problem, open up SQL Server Configuration Manager and it should be easy fix. But then I got another error:
I have run into this error (i.e. 0×80041010) before when I had multiple versions of SQL Installed. However, this was not the case here. Searching on Internet, it quickly became apparent it is fairly common issue and I ran into two useful articles: one by Eric Charran and another on Microsoft Connect.
I tried the fix outlined in Eric’s article but I couldn’t find the MOF file he referenced. Digging into his post and few follow up links from there, I discovered that since I am running Windows 7 Pro x64-bit, my file is in the C:\Program Files (x86)\Microsoft SQL Server\100\Shared\ directory. So I tried executing the command Eric outlined:
mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
It failed again, giving me WMI error 0×80041003, which simply means “Current user does not have permissions to perform the action.” Seeing this, I decided to run same command using “Run As Administrator” on the command prompt and presto, it worked with no issues.
Now I retried SQL Server Configuration Manager and it worked! So thanks, Eric 
. I made my change for the Filestream, came back to the command prompt for the Sample Database, executed the command and everything worked like a charm.
So it got me wondering why my setup failed to begin with. I ran the setup again to try and read the error message (FINALLY); but it worked this time so I didn’t see the error. It seems the Sample Database Installer is trying to read what services are installed on your computer and server so it knows where to install the sample databases when you select these. Duh! Oh well, it was still nice tracking it down and getting it working so I can actually work on my original article.
One lesson (re)Learned: Read error messages more carefully!
Also avalaible on OpsVault.com and MSDN Blog.
Whats the plan?
I don’t have one to be exact. But I am at Microsoft GeekREADY! conference, learning lots of interesting stuff. Some stuff about social networking and over all ePersona. So so I have three blogs now; going to try to do stuff that will help it organize what I am doing.
SQL Canada @ WordPress - Non-Microsoft, just what ever I feel like talking about, but it will be SQL.
SQL Canada @ MSDN (Link) – Experiences as SQL Server PFE (hopefully fun & interesting articles).
OpsVault (Link) – PFE Exclusive Articles, deep technical articles on various topics. I am hoping to get deeper then my first attempt (yeeeh! I hope).
Any how…
I am thinking the posts will work this way (probably will change as my brain sorts out stuff)… Article on OpsVault will filter down to (MSDN & WordPress), Article on MSDN will filter down t0 (WordPress), WordPress, is its own man.. thing… it. Yaa thats it!
Okay 12:40AM, I need to clean up all the posts make sure links and images are good that have been ported over from my old blog home. I might remove some of the older posts, I am unsure. Anyhow for now bed calls, still got GeekREADY! tomorrow.
No wait, it’s now almost 1AM. Just had another idea; WordPress might also include posts from my MSDN Forms and MSDN newsgroups issues I assist with .
Now its time to go sleep, if my brain stops turning.
Finding All Plans/SQL Statements that executed in Parralel
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan AS CompleteQueryPlan
, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText
, n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel
, n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
, n.query('.') AS ParallelSubTreeXML
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
References: #21
Statement copied from The Rambling DBA: Jonathan Kehayias Blog
Get Top 50 SQL Statements/Query Plans
Finding out what statements causing grief on SQL Server has become significantly easier with SQL Server 2005+. I am using some of the DMV/DMF to pull this information together. Please note on a 64-bit system with lots of memory this can take a bit of time to run because of Sort & pulling execution plan information. If that is an issue, I recommend getting the full information without sort by and dumping it to a temp table. From there do your analysis, here are few options for running this statement:
Following query returns everything it is very expensive and takes a while to complete, even if you dump it to tempdb it can take 10+ mintutes on systems with lots of memory.
-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--
DECLARE @Top50By INT
-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1
SELECT TOP 50
SUBSTRING(st.text,
(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN
DATALENGTH(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS SQLStatement
, qp.query_plan AS QueryPlan
, execution_count AS NumOfExecutions
, (total_elapsed_time/execution_count)/1000 AS AVGDuration_ms
, (total_worker_time/execution_count)/1000 AS AVGCPU_ms
, (total_logical_reads/execution_count) AS AVGReads
, (total_logical_writes/execution_count) AS AVGWrites
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
ORDER BY CASE
WHEN @Top50By = 1 THEN
(total_elapsed_time/execution_count)/1000
WHEN @Top50By = 2 THEN
(total_worker_time/execution_count)/1000
WHEN @Top50By = 3 THEN
(total_logical_reads/execution_count)
END DESC
Same code, but I have taken out the execution plan, so it should execute faster. The idea is once you know which queries you want to address those are the only plans you will bring up; making it faster. However this method if you save the data, there is no grantee the plan will be there in the future.
-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--
DECLARE @Top50By INT
-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1
SELECT TOP 50
SUBSTRING(st.text,
(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN
DATALENGTH(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS SQLStatement
, qs.plan_handle AS PlanHandle
, execution_count AS NumOfExecutions
, (total_elapsed_time/execution_count)/1000 AS AVGDuration_ms
, (total_worker_time/execution_count)/1000 AS AVGCPU_ms
, (total_logical_reads/execution_count) AS AVGReads
, (total_logical_writes/execution_count) AS AVGWrites
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
ORDER BY CASE
WHEN @Top50By = 1 THEN
(total_elapsed_time/execution_count)/1000
WHEN @Top50By = 2 THEN
(total_worker_time/execution_count)/1000
WHEN @Top50By = 3 THEN
(total_logical_reads/execution_count)
END DESC
1st Month ast Microsoft…
FAST!
I promise to not turn this into a personal blog and will start posting SQL related stuff again *soon*; just need to get my life sorted out a bit now.
So been with Microsoft for just over a month now and I have to say it is going to be a blast working here if I can survive the pace. So a quick recap on what I been up to …
1st week Janauary met the boss and got my hardware. 2nd week learning the admin processes. 3rd week training (cool stuff!!!) 4th week more learning admin processes and self studying (for next week) 5th week shadowing a co-worker 6th week MY FIRST ENGAGEMENT with client 
.
So 6 weeks have gone by already!?!? OMG I think I forgot to breath some where… eeep!
The process is over whelming at first but I think I am getting hang of it, you really have to learn to walk fast or you get left behind. Making mistakes is part of learning right!?! While I am making a few and learning fast. Can’t sit around, so far if I have spare time I am reading, reveweing. The things “I WISHED” I had time to read, but didn’t; well now I got to read all that and more.
I have read some thing like 1000+ pages of material since joining Microsoft. Technical and non-technical (admin papers zzzzzzzzzz). Process work is nice.. I am suppose to get more of it done soon hmmm where do I find the time. Well keep at it, lets see if I can climb this mountian…
I am glad I got the oppertunity to be here, a new preserctive for me .
Microsoft Community Contributor Award
Heh, Just got the 2011 Microsoft Community Contributor award. Yeeh. Got pretty badges and certificate .
Thanks!!
PS I just like to add, posts on this blog are of my own creation. Even though I am working for Microsoft; please take what I say with grain of salt. Not supported by Microsoft, but I always have tried to and will continue trying to provide correct information on here.
