[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

The database query science

Posted on 2012-04-01
8
Medium Priority
?
241 Views
Last Modified: 2012-04-16
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.
0
Comment
Question by:marrowyung
  • 3
  • 3
  • 2
8 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 400 total points
ID: 37794728
why don't you work with the views of the database?
http://msdn.microsoft.com/en-us/library/ms189741.aspx
0
 
LVL 25

Accepted Solution

by:
jogos earned 1600 total points
ID: 37794754
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
 
LVL 1

Author Comment

by:marrowyung
ID: 37794763
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 37794790
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
 
LVL 25

Expert Comment

by:jogos
ID: 37794799
<<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
 
LVL 1

Author Comment

by:marrowyung
ID: 37794885
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
 
LVL 25

Expert Comment

by:jogos
ID: 37795010
" 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
 
LVL 1

Author Comment

by:marrowyung
ID: 37799336
will get back to you all very soon.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question