The database query science

Dear all,

Right now I keep finding out the tunning advice by from DTA of SQL server 2008 is not that correct.

I start by thinking read more science book on the way SQL enginer parse the SQL so that the output can be fastest.

Any link or books I should read based on your experience ?

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
why don't you work with the views of the database?
http://msdn.microsoft.com/en-us/library/ms189741.aspx
0
jogosCommented:
I agree wint momi_sabag that DMV's can get you a grip on what's happening. A book that get's you started with that quit easy is 'SQL Server DMVs in Action' by Manning Publications.

Query per query the execution plan is the way to understand where it's weakness is
http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu
http://msdn.microsoft.com/en-us/library/ms175913.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
momi_sabag,

 sys.dm_exec_query_stats returned the aggregate performance statistics for cached query plans, it doesn't tell you more and I can tell you that once I use this to benchmark query, the result is not that good. The result can be different from time to time even I restart the SQL service.

This one: sys.dm_exec_request, which you more on real time query which is running.

What I am looking for a the SQL engine science point of view, like select is a sequential operation and becauase of this , RAID IO is better. Something like this.

DBA100.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

momi_sabagCommented:
so for that, you need to run the execution plan and examine it
and from that view i posted, you can get to the detailed execution plan that is actually stored in the cache, and understand from that query plan, why the optimizer decided on a specific access plan
0
jogosCommented:
<<sys.dm_exec_query_stats returned the aggregate performance statistics for cached query plans, it doesn't tell you more and I can tell you that once I use this to benchmark query, the result is not that good. The result can be different from time to time even I restart the SQL service..>>
The aggregation stops when you restart then you start from scratch.   That's where the book I mentioned reveals a bunch of oportunities on how to work with that information. (PS follow the link and look into the download chapter to see if it's revealing some of it's mistery)

<<results can be different from time to time>>
exactly , but your system is different from time to time to.  Sometimes an insert or update needs a table or index to allocate more space or reorganise pages. Sometimes you must wait for locks. If your lucky you get your data from the buffer cache, ... if not it's from disk and you have to hope disk is not over-demanded on that moment. If you have bad statistics the value you ask can play a role, if it fits in the bad statistics then you can have good result, if it doesn't you get bad performance.
0
marrowyungSenior Technical architecture (Data)Author Commented:
jogos,

"exactly , but your system is different from time to time to.  Sometimes an insert or update needs a table or index to allocate more space or reorganise pages. Sometimes you must wait for locks."

I just execute the same thing over and over again, but the result can different. I restart the SQL service but it still give the same different result. This make me can't compare it further.


"If you have bad statistics the value you ask can play a role, if it fits in the bad statistics then you can have good result, if it doesn't you get bad performance."

It should be bad statistics, bad result. Good statistics good result, right?

I will read your link, thanks.

DBA100.
0
jogosCommented:
" the value you ask can play a role, if it fits in the bad statistics then you can have good result"
Meaning that if that value fits the direction of the (old or badly sampled) statistics then you can have still good resulds, it does not mean that the statistics are good.
0
marrowyungSenior Technical architecture (Data)Author Commented:
will get back to you all very soon.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.