Solved

The database query science

Posted on 2012-04-01
8
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 100 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 400 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

628 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