June 27, 2014 at 8:07 am
I am wondering if I can get some help from the community. I have this query which is taking up good amount of CPU. I just want to know if its normal or not. I also don't know if logical reads are good or bad. What are all those numbers under CPU, Logical, Physical etc. :unsure:
SELECT TOP 10001 T508.C1,C875061025 FROM T508 WHERE (T508.C875061023 = N'BFGAA5V0HEDP9ANF9FL0FYTAYBGQMP') ORDER BY 1 ASC
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 27, 2014 at 10:37 am
It depends.
But the answer to other question is:-
Logical Read: Number of pages read from data cache.
Physical Read: Number of pages read from the disk.
CPU time refers to the actual time spend on CPU for Query.
June 27, 2014 at 10:38 am
Those questions are largely subjective and dependent upon what the query is designed to do, what the size of the data is and how quickly this thing is supposed to operate. If you're unclear on what some of those numbers mean (e.g. Logical Reads, Physical Reads), there are some good articles out there on query execution plans an IO statistics.
http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/
That said, seeing a query with over 2 billion logical reads (i.e how many 8k pages SQL has to read to get this information) ties my stomach in knots. Take this with a grain of salt, but my guess is "no, that's not normal" or at very least has room for optimization.
June 27, 2014 at 11:07 am
I ended up adding a non-clustered index and it went from 11000 logical reads to 3 logical reads.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 30, 2014 at 4:49 am
You really want to look at the execution plan on something like this. There is no obvious issue with your T-SQL, so you read the execution plan to understand how the optimizer resolved it with your existing objects.
Now that you have a nonclustered index, look to see if you also have a key/rid lookup operation. That might be a place where you can further tune the situation.
"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
June 30, 2014 at 11:20 am
I Just became a SQL Server DBA about 8 months ago but don't have any other DBA in the company who I can talk to :ermm: Until last week, we never did any performance related drills since none of the DB is more than 16GB. Plenty of memory (at least 8GB for all), so we don't typically run into any performance issues. I just attended this Webinar last week which mainly focused on Troubleshooting and performance related issues. After attending the webinar, I started poking around and found out that we can run into performance related issues in the future, and the attachment was from one of the DB. I am using Activity Monitor to see "Recent expensive queries" and finding out that almost every other query is taking up CPU and producing 1 or 2 records where logical reads are sometimes more than 50,000. (Enough of the information)
Question:
Is using activity monitor the best way to find out which queries are taking up cpu etc or using some sorts of scripts?
I don't have any monitoring tool which I can use to see what's happening unless I am staring at the activity monitor to see most recent queries.
I feel it is part of my job to prevent something from happening, but how would I do that? Unless I do this to every environment, watch activity monitor closely and then add Indexes recommended by the execution plan? Or if there is anything else I should do?
This is another query from another DB
SELECT COUNT(*) FROM T702 WHERE ((T702.C875000000 = N'AA1.AY114001') AND ((T702.C675218000 = 2) OR (T702.C675218000 IS NULL)))
See attachments with Execution Plan
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 30, 2014 at 1:36 pm
Tuning is a cyclical process that you just keep doing and doing. Basically you monitor a server, gather metrics on what's performing well, what isn't, what normal looks like, and then fix the ones that are slow. Wash, rinse and repeat. Activity Monitor is somewhat adequate. You'd be better off looking to dynamic management views like sys.dm_exec_query_stats which shows an aggregate of the queries currently in cache. You can get the top 10 slowest queries and other things from there. You do that for each server. Same thing goes for pulling execution plans out of cache. There are lots of other ways you can build your own monitoring tool. It takes time.
The plan you posted shows that you're pulling stuff from a view that seems to be referencing one table over and over, T501 (frightening naming convention by the way). There are a number of tuning opportunities there. You've got scans against some indexes plus key lookups. I didn't drill down on anything, but you may want to examine the query in the view in addition to looking at indexing opportunities.
If you do want to learn tuning, I strongly suggest my books. The execution plans book can be downloaded for free (just make sure you're getting Version 2).
"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
June 30, 2014 at 1:46 pm
T501 (frightening naming convention by the way).
I have no idea why we can't follow a proper guideline on how to name the table, but I usually keep my mouth shut:(
If you do want to learn tuning, I strongly suggest my books. The execution plans book can be downloaded for free (just make sure you're getting Version 2).
I ended up downloading the 2nd version of the book and I am sure by the time I am done reading this, I will be able to understand the best way to tune trouble queries.
Thanks.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 1, 2014 at 4:38 am
New Born DBA (6/30/2014)
I ended up downloading the 2nd version of the book and I am sure by the time I am done reading this, I will be able to understand the best way to tune trouble queries.Thanks.
Thank you. Best of luck.
Just so we're clear though, the execution plans book won't help that much with query tuning. It's pretty focused on how to interpret execution plans. The other book (not free I'm afraid) is all about query tuning.
"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
July 1, 2014 at 10:56 am
Grant Fritchey (7/1/2014) The other book (not free I'm afraid) is all about query tuning.
I don't mind buying the book. As my father always say, you never waste money on food and books.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply