Solved

The database query science

Posted on 2012-04-01
8
229 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 25

Expert Comment

by:jogos
Comment Utility
<<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
Comment Utility
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
Comment Utility
" 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
Comment Utility
will get back to you all very soon.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now