April 29, 2015 at 9:00 am
Hi All,
I am a dot.net programmer, not a DBA, and have inherited an 2012 SQL Server database that is poorly performing. Joins, reports and scripts are slow to painfully slow.
Based on what little I know to look at -
First thing I see is that most tables are Heaps
Next most tables don't have Primary Keys or indexes
When indexed, they are using GUIDs as the keys
The database is about 42Gb
Workload - record test results - relatively low record volume but records are potentially long
Environment: x64 with 8Gb memory, Windows 2008, SQL Server Enterprise
What else should I be looking at?
Are there any decent assessment tools or scripts either built in to SQL Server or available from qualified sources?
Thank you in advance
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 29, 2015 at 9:05 am
You can run the Database Tuning Advisor, but be careful with the recommendations. Go slow and add one index at a time. This will give you some ideas of what's missing for indexes, and then you can use those with some knowledge of the number of times each statement runs to focus your efforts. Indexing will likely give you the biggest bang for the time spent at first.
Ask questions back here for specific tables/indexes/queries. Knowing the execution plan for a query and frequency of execution (as well as similar queries) will help us give you other recommendations.
April 29, 2015 at 9:21 am
Waittypes of the queries (IO/NETWORK/CXPACKET/...)
Can be done with sp_Whoisactive or the dynamic management views.
April 29, 2015 at 9:48 am
Thanks Steve,
My first instinct was to get them off of non-sequential GUIDs and use Integer identities - but they are both surrogate keys so maybe not much difference other than readability and 4 byte vs 16-byte join processing
Only 8Gb of memory seems to be below what I usually see
Programming calls are all via dynamic SQL, suggesting they go to stored procedures and/or views since most usage is pretty standard.
Will give the DTA as run and see what shakes out
Thx again
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 29, 2015 at 9:54 am
For a 42gb database, 8gb of memory is not that low really. But, it does depend on the types of queries and everything else.
I put nearly zero faith in the DTA. It might prove useful in this case since you're dealing with all heaps, but even so, implement it's suggestions with a giant grain of salt. I would be especially leery of any suggested statistics.
As to whether or not the GUIDs are causing you pain, the question is are you doing point lookups (retrieving a single row), or scans? If point lookups, they're probably OK. If scans, fragmentation could be killing you.
One other thing to consider is that your statistics could be out of date. While they are automatically maintained by default, that can be turned off, and the automatic maintenance can be inadequate. You might consider running sp_updatestats just to clear the pipes as it were.
For lots more information on how to gather query metrics, identify the slow running queries, and understand what to do about them, I really recommend getting a copy of my book in the signature below.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2015 at 12:38 pm
Hi Grant -
I appreciate your response, and all your knowledge and experience. Maybe I'm short-cutting or lazy here but I was hoping for a shorter solution/crash course(!:w00t:) than reading through 100's of pages of technical writing. I know this is a complex topic and maybe that's the only way to do it. I'm sure it would be if I had more time...
Probably Microsoft had me in mind when the put the DTA together, even if it doesn't work as well as hoped.
I was hoping for a checklist and few scripts to get a handle on the general health and configuration (or lack of), with some BKM's to work on fixing the problems.
I will load your book on my Kindle and see what I can figure out, I'd appreciate suggestions on where to start in the book (if other than page 1...)
Thank you
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 29, 2015 at 4:04 pm
Tom_Sacramento (4/29/2015)
Hi Grant -I appreciate your response, and all your knowledge and experience. Maybe I'm short-cutting or lazy here but I was hoping for a shorter solution/crash course(!:w00t:) than reading through 100's of pages of technical writing. I know this is a complex topic and maybe that's the only way to do it. I'm sure it would be if I had more time...
Probably Microsoft had me in mind when the put the DTA together, even if it doesn't work as well as hoped.
I was hoping for a checklist and few scripts to get a handle on the general health and configuration (or lack of), with some BKM's to work on fixing the problems.
I will load your book on my Kindle and see what I can figure out, I'd appreciate suggestions on where to start in the book (if other than page 1...)
Thank you
Ha!
Sorry. I wish there was a shortcut to this, but unfortunately I haven't found one.
One suggestion that will identify a lot of easy to fix, low-hanging fruit and, to my knowledge, won't suggest anything as insane or useless as the DTA sometimes does, would be SQLCop [/url]from the people who run the lessthandot.com web site. It's a freebie. I wrote a review of it a couple of years ago[/url]. It'll help.
Unfortunately, the real hard work will still be there. Identify the longest running and/or most frequently called queries, understanding why they're hurting, knowing what to do about it, no short cuts unfortunately. Just identifying the best candidates for a clustered index (and almost every single table should have a clustered index) requires some investigation (what is the most common access path to the data on this table?).
Sorry again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2015 at 4:05 pm
Tom_Sacramento (4/29/2015)
Hi Grant -I appreciate your response, and all your knowledge and experience. Maybe I'm short-cutting or lazy here but I was hoping for a shorter solution/crash course(!:w00t:) than reading through 100's of pages of technical writing. I know this is a complex topic and maybe that's the only way to do it. I'm sure it would be if I had more time...
Probably Microsoft had me in mind when the put the DTA together, even if it doesn't work as well as hoped.
I was hoping for a checklist and few scripts to get a handle on the general health and configuration (or lack of), with some BKM's to work on fixing the problems.
I will load your book on my Kindle and see what I can figure out, I'd appreciate suggestions on where to start in the book (if other than page 1...)
Thank you
Oh, and thanks for the kind words even though I wasn't a bit of help.
And as far as the book goes, most people can skip to Chapter 6.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2015 at 4:08 pm
Thank you - will check on SQLCop.
The system is very messy, lots of Heaps, few indexes, nchars, nvarchar with ansi padding off - should be able to help them. Back to reading....
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 29, 2015 at 9:35 pm
Read Grant's book, that certainly will help.
I know Grant doesn't like the DTA, but I do think it at least gives you some ideas. Do not implement lots of its suggestions, but I think using them as a guide isn't bad. It can process a large workload and let you know what's being run often.
In terms of SQL Cop, that's built on tSQLt. You can restore production somewhere and run it, but DO NOT put tSQLt on production. Not at all suited for that environment
April 30, 2015 at 6:54 am
Steve and Grant - thank you both. I much appreciate your guidance and suggestions, I'm sure that I can learn some important things and help my client - and that's the fun part 🙂
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 30, 2015 at 7:20 am
Well, you've certainly got your work cut out for you. As someone who comes from an application developer background, I'm sure you know how folks feel about database refactoring. You're now THAT guy.
Given the scenario, perhaps the best place to start would be with a query for top 10 most expensive execution plans. Specifically something that leverages sys.dm_exec_query_stats to return reads, writes, elapsed time, worker time, etc.
This one converts milisecond time to hh:mm:ss format and also includes execution plan, which you can paste into SQL Sentry Plan Explorer.
SELECT TOP 10
OBJECT_NAME(qt.objectid) object_name
,case when qs.statement_start_offset is not null
then
substring(char(13)+SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((
CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END
- qs.statement_start_offset)/2) + 1),1,8000)
else qt.text
end as statement_text
,creation_time
,last_execution_time
,execution_count
,convert(varchar,dateadd(ms,last_elapsed_time,getdate())-getdate(),108)last_elapsed_time
,convert(varchar,dateadd(ms,last_worker_time,getdate())-getdate(),108)last_worker_time
,convert(varchar,dateadd(ms,last_elapsed_time - last_worker_time,getdate())-getdate(),108)last_blocked_time
,last_logical_reads
,last_physical_reads
,last_logical_writes
,last_rows
,query_plan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
-- where qt.text like '%insert%MyTable%'
-- where OBJECT_NAME(qt.objectid) = 'usp_MyProc'
order by (qs.last_elapsed_time - qs.last_worker_time) desc;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 30, 2015 at 7:28 am
What I posted previously is ranking top 10 queries by wait (blocked) time. How we define queries as "expensive" depends on where your most urgent bottleneck is. You could perhaps order by total_worker_time, total_elapsed_time, or total_physical_reads.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 30, 2015 at 9:27 am
Thanks Eric
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 5, 2015 at 6:18 am
You might want to dig under the hood more because indexes and primary keys are the easy part IMHO.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply