July 24, 2018 at 8:28 am
Hi all,
I have been working in TSQL for 7 years but till now i have never faced to tune a query or i have never been into tuning query. Now i am trying to find another JOB and so i am unable to explain what i do for query tuning.
So can some one help me with good link or article that shows different scenario on Adventure work (which has real time data )how to tune query and what are different type of technique used in real time to tune query in depth.
And what are the steps i should follow to tune query if i face slow running query.
In other words , I need to learn about all operator(index seek ,scan, key look up , table spool ext ) why it occurs in estimated execution plan or actual execution plan and how to over come some of operator which reduces performance.
thanks in advance
July 24, 2018 at 8:42 am
JoNTSQLSrv - Tuesday, July 24, 2018 8:28 AMHi all,
I have been working in TSQL for 7 years but till now i have never faced to tune a query or i have never been into tuning query. Now i am trying to find another JOB and so i am unable to explain what i do for query tuning.So can some one help me with good link or article that shows different scenario on Adventure work (which has real time data )how to tune query and what are different type of technique used in real time to tune query in depth.
And what are the steps i should follow to tune query if i face slow running query.
thanks in advance
It's a very big topic. You might find this ebook a useful place to start.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 24, 2018 at 8:55 am
Phil Parkin - Tuesday, July 24, 2018 8:42 AMJoNTSQLSrv - Tuesday, July 24, 2018 8:28 AMHi all,
I have been working in TSQL for 7 years but till now i have never faced to tune a query or i have never been into tuning query. Now i am trying to find another JOB and so i am unable to explain what i do for query tuning.So can some one help me with good link or article that shows different scenario on Adventure work (which has real time data )how to tune query and what are different type of technique used in real time to tune query in depth.
And what are the steps i should follow to tune query if i face slow running query.
thanks in advance
It's a very big topic. You might find this ebook a useful place to start.
You could also invest on one of the Query Performance Tuning books by Grant. Choose accordingly to the version you plan to use.
https://www.amazon.com/s/ref=dp_byline_sr_book_1?ie=UTF8&text=Grant+Fritchey&search-alias=books&field-author=Grant+Fritchey&sort=relevancerank
By the way, I'm not getting anything from promoting his books, I just happen to have liked them.
July 24, 2018 at 9:00 am
JoNTSQLSrv - Tuesday, July 24, 2018 8:28 AMHi all,
I have been working in TSQL for 7 years but till now i have never faced to tune a query or i have never been into tuning query. Now i am trying to find another JOB and so i am unable to explain what i do for query tuning.So can some one help me with good link or article that shows different scenario on Adventure work (which has real time data )how to tune query and what are different type of technique used in real time to tune query in depth.
And what are the steps i should follow to tune query if i face slow running query.
In other words , I need to learn about all operator(index seek ,scan, key look up , table spool ext ) why it occurs in estimated execution plan or actual execution plan and how to over come some of operator which reduces performance.
thanks in advance
I think Adventure Works is not going to help you too much here, it is a relatively small and simple data set. My recommendations are to learn how you establish and collect metrics, isolate individual work efforts etc., i.e. how do you know if the I/O subsystem is slow, the query is doing something silly, the operator is impatient etc.
😎
Diagnostics and analysis are the keys to solve the performance problems, not specific tool sets, which by the way can be acquired as needed.
July 24, 2018 at 10:21 am
The more modern sample database to use is World Wide Importers:
https://blogs.msdn.microsoft.com/samlester/2016/06/13/so-long-adventureworks-well-miss-you-and-hello-world-wide-importers/
However if you are really on SQL Server 2012 and want to stay with AdventureWorks, maybe look at making the dataset bigger so it's easier to actually find, test, and fix performance issues:
https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/
July 24, 2018 at 12:42 pm
Luis Cazares - Tuesday, July 24, 2018 8:55 AMPhil Parkin - Tuesday, July 24, 2018 8:42 AMJoNTSQLSrv - Tuesday, July 24, 2018 8:28 AMHi all,
I have been working in TSQL for 7 years but till now i have never faced to tune a query or i have never been into tuning query. Now i am trying to find another JOB and so i am unable to explain what i do for query tuning.So can some one help me with good link or article that shows different scenario on Adventure work (which has real time data )how to tune query and what are different type of technique used in real time to tune query in depth.
And what are the steps i should follow to tune query if i face slow running query.
thanks in advance
It's a very big topic. You might find this ebook a useful place to start.
You could also invest on one of the Query Performance Tuning books by Grant. Choose accordingly to the version you plan to use.
https://www.amazon.com/s/ref=dp_byline_sr_book_1?ie=UTF8&text=Grant+Fritchey&search-alias=books&field-author=Grant+Fritchey&sort=relevancerank
By the way, I'm not getting anything from promoting his books, I just happen to have liked them.
Thank you!
Although, with the exception of the newest version (coming out in September), I'd suggest going just for the latest one, Fourth Edition. It has all the stuff from the earlier ones plus more. The next version, I remove a bunch of older stuff in order to make room for newer things, so it's not going to be as useful for people working on 2008R2 or lower. The Fourth Edition has all that stuff.
And yeah, almost every example is in AdventureWorks.
"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 24, 2018 at 12:43 pm
Chris Harshman - Tuesday, July 24, 2018 10:21 AMThe more modern sample database to use is World Wide Importers:
https://blogs.msdn.microsoft.com/samlester/2016/06/13/so-long-adventureworks-well-miss-you-and-hello-world-wide-importers/However if you are really on SQL Server 2012 and want to stay with AdventureWorks, maybe look at making the dataset bigger so it's easier to actually find, test, and fix performance issues:
https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/
World Wide Importers is a vastly superior database to AdventureWorks.... Which is why I hate it. It has better structures, better data distribution, better data types, better naming standards... in short, it looks nothing like real databases. I like AdventureWorks because it is horribly flawed, like all the databases we see online here every day.
"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 26, 2018 at 3:22 am
Thanks all for your useful reply.
Specifically thanks to Phil Parkin & Luis Cazares for Ebook. Hope this will help me in what I exactly need to learn
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply