Guide as to how to go about SQL Server 2K performance tunning and optimization
-
Yeah - there are definitely some sharp guys out there consulting that are unfortunately not very good in the 'mentor' roll. That is a pretty good performance gain (14 to 3). I find it curious though that you measure your batch processing times by MINUTES per ROW!! Batch processing times should almost always be measured per some large number of rows (such as 10K or 1M), and are most often (certainly best) measured in seconds. :-)) I must wonder if the expert simply increased the performance of your cursor(s) instead of rewriting your processing to use set based logic. I suppose that you could have one of those 1-in-a-million situations where logic must be row-by-row.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Hi TheSQLGuru,
I am sure, I must have got it wrong. As I addmited earlier, yes I have been ROW based thinker so far but as pointed out by Jeff, I am getting into SET based. I am really new at this and not any good at it right now.
Well, just two weeks old, hopefully I get better with time.
Thanks for pointing it out to be. I will defn ask him and post what exactly he has done.
Regards
Sree
-
Here is the list Jeff mentioned early in the post (I may post more once I get through the thread:
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy, you are likely doing things backwards I think, especially in this case where there may well have been NO tuning/indexing done in the first place (given that a .NET guy has to do the tuning). updating usage/stats is only of minimal value assuing autostats is on (which should be verified). Likewise for reindexing. Not very helpful if there aren't any indexes created.
) Oh, and doesn't usage/stats updates cause locking? They certainly flush useful data out of RAM. Those full scans can bring a production system to it's knees.I pretty much START with profiler. Knowing the code and access patterns and also doing statistical analyses by loading the profile runs into a table allows you to prioritize and get the biggest bang for the effort. It also allows you to check the data to see if indexes will even be helpful by knowing which fields to analyze for distinct value counts. Remember that the optimizer won't use an index if more than 8-15% of the total rows would be returned. Analyzing the profile will also allow you to find opportunities for compound indexes or INCLUDEd columns to cover.
I am actually doing this very thing for a client today!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
The "Short List" makes a few realistic assumptions. These are:
- The database is in production
- The DBA has more than one database to care for
- The DBA has more than one server to care for
- The DBA is a limited resource
Now based on these criteria, we need to apply the principal of gaining the most with the least amount of effort - hence the "Short List" !
Now if you are consulting and have only one database or system to look out for then by all means profile it to blazes if that is what the client needs and the boilling will be supported. However you can also use a lot of time fixing things that are not really broken simply because some or all of the "Short List" is not in place. One example - AutoUpdate Statistics sounds great but unless all of your SPs are created with RECOMPILE (a waste all in its own right) then you will not get an updated execution plan until the SQL Server is restarted !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
1) Your "realistic assumptions" still have a fundamental flaw. Many (most in my experience and also from students in the classes I teach) Microsoft shops don't have even a SINGLE DBA. :-)) And the ones that do usually have one that is not very well trained/knowledgeable because they still have other duties they spend most of their effort on.
2) Your statement about autoupdate statistics not generating updated execution plans until SQL Server is restarted is also in error. From SQL 2K5 BOL, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3d3ea8fc-fb57-4e06-8d47-3b074c1bc0b8.htm
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query. The conditions that invalidate a plan include the following:
- Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
- Changes to any indexes used by the execution plan.
- Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
3) I do not "use a lot of time fixing things that aren't really broken simply because some or all of your shortlist isn't in place". I have spent tens of thousands of professional manhours working solely with SQL Server and thousands of personal manhours learning to be very good at it. My "SQLGuru" login on this forum is actually earned. 😉
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Remember that the optimizer won't use an index if more than 8-15% of the total rows would be returned. Really?
--===== Create and populate a million row test table.
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeID = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),
SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),
SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
INTO dbo.IndexTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Create an index for the test
CREATE INDEX IDX_IndexTest_SomeID_SomeString ON dbo.IndexTest(SomeID, SomeString)
--===== Select more than 8-15% (~80% actually) of the table to show that an Index is still used
-- Use either an estimated execution plan or a real one... same result... INDEX SEEK
SELECT SomeID, SomeString
FROM dbo.IndexTest
WHERE SomeID <= 40000
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, really. Your fabricated example uses a covering index and thus has no need to touch the base table. Change your select to this:
SELECT SomeID, SomeString, somedate
FROM dbo.IndexTest
WHERE SomeID <= 40000
and you will see that instead of doing 4331 (on my system) reads using a table scan costing 4.31 forcing the usage of the index (which the query optimizer will obviously not do) thusly:
SELECT SomeID, SomeString, somedate
FROM #IndexTest with (index=IDX_IndexTest_SomeID_SomeString)
WHERE SomeID <= 40000
results in 801697 reads costing 145.64.
The switchover point cost wise is SomeID <= 87, which costs 4.77 and uses 1786 reads to bring back 1778 rows using index seek/bookmark lookup. SomeID <= 88 uses a table scan for 1801 rows on my system. Thus in this case the optimizer actually ignores the index at 1801/1000000 or 1.8%. In this case the table is extremely small (both in number of rows and in average row size) and thus the cost of a table scan is quite cheap compared to the index seek/bookmark lookup mechanism. Even removing SomeString from the index and thus making it a tighter, smaller index doesn't change the switchover point. I also tried 10M rows and got the same results.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Nope... you made an unqualified statement with no conditions. And no one would force such an index as you have because everyone knows the bookmarks will kill any hope of performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As to the BOL citation, it is 2K5 not 2K as the post is directed to - there is a difference in the documentation, behavior and experience. Again, the heading of the post is directed to 2K. Although I thank you for the education in another subtle 2K5 difference.
Now you are also correct in the statement that most MS shops do not even have a dedicated DBA. My 20+ years as a DBA have been quite productive and very fruitful due to the lack of forsight of these businesses. It has been my pleasure to educate them and be given the privilege to help them build solid and successful environments, standards, applications, and most importantly DBA teams. I definitely thank these organizations for the opportunities and I'm sure that you do too seeing as you are trying to make a present client's application and database perform the way it should.
TheSQLGuru ... you've really got me thinking here - I love spirited discourse. I am not doubting your earnest nor your experience but wish to advocate in the 'SQL Way' - that there is "more than one way to write a query". Tonight I have the time since a major business merger/conversion is taking place where I work that I am on call for. I have number of StarGate and Star Trek episodes on the DVR to help pass the time while working on this post as well. Maybe I'll even get to The Twilight Zone and The Outer Limits by dawn before I go back to work.
Now lets get to the real crux of tuning. 90% of the performance is in the physical design of the database and its objects. The other 10% is in hardware, application structure, and finally query configuration. Oops, almost forgot that infinitessimal part, SQL Server configuration and database configuration as well. You will notice that I have purposely omitted hardware configuration as related to the actual SQL Server and its storage. There are many god posts out on this site with tremendous discrourse.
Physical database design - tables, primary keys, indexes and normalization. We will not belabor the finer points of nomalization and Boyce Codd incidentals. We have a logical design of data elements that turns into a physical design of tables and relationships, usually 3NF. Oh, but wait, not always. We must consider the actual application and its business needs. Is it OLTP ? Is it Batch ? Is it DSS ? Is it a combination of Online and Batch ? But I digress, these questions are just fodder for the Boyce Codd incidentals. The database or databases will have tables. Each table should have a primary key. Each primary key needs an index on that column at a minimum. If the is DRI then the target table(s) must also have an index(es) on the appropriate column(s). The question of clustered and non-clustered indexes and their usage in and of itself could lead to another extensive post dependent on the type of application (OLTP, Batchm DSS, combination). You could roll-your-own queries to check these things but the quick and efficient way is to use a reverse engineering tool with reporting to do it quickly and efficiently every time. The evaluation version of Sybase PowerDesigner works well for SQL 7, SQL 2000, SQL2K5, Sybase, Oracle and DB2 (All of which I support). ErWin is another good choice. Now check the reports to make sure every table has a key or primary key and an index on it, Also check that each primary key foreign key relationship has an index in each side of the relationship. Yes I realize that you mentioned tables without indexes but notice that I have not even thought of using Profiler yet !
Application configuration - Is it 2 tiered or 3 tiered ? Does it utilize the IIS ? Is the database server a stand alone entity dedicated to SQL Server ? Does the application use separate connections or connection pooling ? Is the application executed on a LAN or a WAN or a combination of both ? Each of these questions and the answers that spring forth from them can be performance issues even before you reach the database server. Again Profiler is not even mentioned.
Query Configuration - Are cursors being used ? Is heavy use of tempdb tables in play ? Is the application using SPs or dynamic SQL ? If it is using dynamic SQL then I must concede that it is time to use Profiler. But things (performance and plans) will change based on values and table sizes. Now if the application is using SPs have the developers attempted to view execution plans at all ? Do you have any application business functions that are doggedly slow ? If these SPs and application components are identifiable then you still do not even have to touch Profiler yet. However if you want to go down the Parameter Sniffing road then I again concede that it is time for Profiler.
Well the merger/conversion is calling ... I'll have more to post on this later.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
>>
Nope... you made an unqualified statement with no conditions. And no one would force such an index as you have because everyone knows the bookmarks will kill any hope of performance.
--Jeff Moden
>>
1) The "did so . . ." comment deserves no reply.
2) Microsoft gave use the ability to force an index precicely because sometimes (granted VERY infrequently) it is the correct thing to do.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Rudy, your post is certainly in the top ten best posts I have ever read on any forum. It was well thought out and worded, thought provoking and also focused on the thread topic.
One reason I skipped evaluating the schema/physical aspects of the database is that they so often are not able to be changed, despite the enormous gains doing so could achieve. There is 1) not enough resources to develop/code the change (almost always the case in small Microsoft-centric shops) or 2) utilized by legacy/packaged apps that cannot be modified. Often adding an index to an existing structure is the only recourse, and the best way to determine what index(s) to add is by analyzing profile runs and the data itself.
I have often wondered if the Sybase PowerDesigner was a good product. Never had the chance to give it a go. I use Embarcadero's ERStudio for my complex designs and reverse engineers.
There is essentially the same quote I gave in the SQL 2000 BOL as well. Type Query Plan in the Index and look down at the bottom. 🙂
Best of luck with the Star Trek watch . . . er conversion! LOL
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
2) Microsoft gave use the ability to force an index precicely because sometimes (granted VERY infrequently) it is the correct thing to do.
Yep, just not in this case.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply