| Author |
Topic |
|
Vishal_sql
Posting Yak Master
102 Posts |
 Posted - 2012-07-13 : 04:58:16
|
| Hi All, I am working as SQL Server Programmer on ERP software with SQL Server as back end and has Crystal Report as front end. I have been told to tune DB as the Report's are taking long.Reports have stored procedure whose code is about average 2K to 3K lines and using Cursors in each report. Now I want to know from Where should one starts for DB tuning.I have never tunned any DB before. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-13 : 07:11:47
|
| Probably it is the cursors in the stored procedures that are causing the performance problems. If that is so, there may be very little you can do short of rewriting those stored procedures. Nonetheless, you should make sure that other aspects of the database - indexes and statistics - are maintained properly.To check if you need index rebuilding or reorganizing, see this page, in particular, the section about detecting fragmentation: http://technet.microsoft.com/en-us/library/ms189858.aspx Similarly, for statistics, see here: http://msdn.microsoft.com/en-us/library/ms190397.aspx#UpdateStatisticsIf that does not help much, I would start by looking at the report/stored proc that causes you the greatest agony. Look at the code to see what/why it is taking too long. It may be that you need to rewrite it, or it may be that there are no proper indexes on the tables etc. But, hard to tell without doing that kind of analysis. |
 |
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-13 : 09:02:27
|
| Thanks Sunita,the links will surely help me.But When I saw Index Physical Statistics in SSMS.I found for most of Index whoes depth is greater than 1 (2,3)the operation recomended is Rebuild or Reorganize ( almost all). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-13 : 09:58:55
|
| why do you need so many cursors? did you analyse and see if it can be converted to set based code which will perform faster?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-14 : 12:04:41
|
| Hi, I would like to do the same but as of now I have been told to Tune the SQL Server DB as it has not been done since the ERP is installed and database is designed. Right now I have found out the Index Physical Stats and looks as if I need to Reorganise/Rebuild the Indexes and then i could look for Queries to tune. Please suggest in order to Tune the DB. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-14 : 19:40:15
|
| ok. but still if you've cursor logic which runs over billions of rows of your table, it can be bottleneck even if you've best of the indexing strategy. so if possible analyse and see if they can be rewritten using set based approach.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-15 : 13:03:42
|
| Can I get suggestion of how to find bottleneck/time taking queries in stored procedures. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-15 : 17:42:42
|
You can run a query like this to get the information about your most intensive queries:SELECT TOP 10 *FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text (s.sql_handle) tORDER BY s.last_elapsed_time DESC There are other columns related to performance, take a look and you may find some other column to order the results by that is a better measure for you. You have to look at the text column to figure out the name of the stored procedure. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-07-16 : 01:31:55
|
| Once you have the list of sql statements - analyse the Exceution plans. There are a number of clues you can look for - which hint at problems - such Clustered Index Scans , Hash Joins etc. This does not necessarily mean you can avoid the Scans - as you may have logic which requires a Scan , such as the use of LIKE '%bbb%'. Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-16 : 03:29:40
|
| Hi,Thanks Sunita and Jack,Intresting replies. |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-17 : 05:58:28
|
| HI Again,I was doing REBUILD of Indexes on each table ( which are required to be done on analysis of fragmentation percent in Index physical stats)I am using ALTER INDEX ALL ON schemaname.tablename REBUILD WITH FILLFACTOR= @fillfactorI searched on blog some used @fillfactor as 80,90,100 Can I know What is genuine fillfactor value and reason for that. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-07-17 : 06:04:44
|
| it depends on frequency of updates. For columns where updates are frequent, for these column indexes go for 80 fillfactor. It will insert values in pages upto 80% and remaining 20% will be left for updates (this would avoid page splitting on updates and would help your indexes will be less fregmented). And columns with sequential and less or no updates, rebuild their respective indexes with FULL page i.e. 100.--------------------------http://connectsql.blogspot.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-07-17 : 06:32:55
|
you have 2 choices:1) Spend time tuning the indexes / adding indexes for there reports.2) Rewrite the reports in a set based way.The benefit / Cost breakdown is:Spend time tuning the indexes / adding indexes for there reports.Probably a small benefit but not too hard to do. Its easy to do - you could probably hire a consultant to put a index health strategy in place for only a small outlay.Rewrite the reports in a set based way.Very likely a HUGE reduction in query time. However, you will need to understand the reports and to be able to re-write them. If you don't have the skills internally you may need to hire someone for an extended period of time and you'd need someone *good* at this.Does anyone at your company understand how the reports work?Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-17 : 07:06:35
|
Hi Charles,I Agree with you on 2 nd option,but there are many reports and each ones logic need to undestand properly from Senior Advisor before going for re writing and also needs to verify the results.needs lot of time and resources.Am here single person working on the Reporting. I think I need to go report by report |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-17 : 07:08:34
|
| Thanks Aasim for those info. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-07-17 : 07:18:52
|
if you are able to, feel free to post code. Cursors are like blood in the water -- somewhere a set based shark is lurking.... ready for the kill.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-17 : 08:04:13
|
quote:
Originally posted by Transact Charlie
Cursors are like blood in the water
Heh! Good one Charlie, I have to remember this!!  quote:
Originally posted by Vishal_sql
Am here single person working on the Reporting.
Vishal, I hope they are paying you a lot of money :)Even if the whole task looks daunting and time consuming, you might want to consider picking the most time-consuming report/query and rewriting the code for that using set-based queries. Then, when there is a gigantic improvement in performance, take it to the management and dazzle them with it. You would be the star, and they would beg you to do the same for other reports!And, like Charlie said, if you run into difficulties making the queries into set-based, ask for help here, this place is teeming with people who are exceptionally good at that. When you post, simplify the problem and post it with enough information (sort of like what is described in Brett's blog here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-07-17 : 08:48:31
|
hahahaHey Sunita,i dont want to debate how much they pay for me but m considered as fresher . And i am not sure management would beg . But I will try to do . Lets see.Thanks. |
|
|
|
|
Database tuning