Solved

The database query science

Posted on 2012-04-01
8
232 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

830 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