SQL 2000 - Performance suggestions
-
I have a customer using SQL 2000 Developer Edition on W2003 Std. The server has 2 x 3.75Ghz Zeon CPUs with HT enabled, 4GB RAM and a 400GB Raid 5 Array. The database in use is just under 10GB in size (SQL is configured to use AWE). All indexes in the database are rebuilt every night but they have constant performance issues even though there are only a maximum of 40 concurrent users at any time. A simple query that returns 20 fields from a customer table (156,000 records in total) with 4 subqueries returning the max/min order dates etc from the order table (620,000 records in total) takes just over 4 hours to run.
I checked the indexes etc, is there any hardware performance tips that could help? All suggestions welcome!
-
Do the execution plans for the queries look well-optimized? Are you sure they're using the indexes?
I don't know what the restrictions are on the developer edition, but I'm guessing that it wasn't really meant to support 40 concurrent users.
Are all the service packs installed?
And then again, I might be wrong ...
David Webb -
The execution plans are using the correct indexes as far as I can see
SP4 is installed - I'm just trying a disk defrag at the moment before doing an index rebuild, the disk looked very fragmented
-
Would it be possible to get the DDL for the tables (including indexes) and the query in question?
-
Looks like someone had added an expensive update to the summary table which used a cursor and sent values into a function. I simplified this with a single statement which now means the query runs in under 10 mins.
On another note I rebuilt all the indexes using a database maintenance plan last night - As I understand it that runs a DBCC DBREINDEX against each table which should recreate the index. However, after this ran, I checked the fragmentation of each index using DBCC SHOWCONTIG which showed that the majority of indexes where fragmented between 90 - 100%?! Does this seem normal??
-
If there are regular maintenance windows during which the database/SQL can be taken offline, it might be a good idea to take some time to defragment the filesystems where the data files and transaction logs reside. (Particularly if things are set to grow automatically, and possibly shrunk in maintenance)
To do so, you'll need to make sure that nothing has its teeth into the files in question, so that they may be defragged effectively. (you may be able to simply detach the db, however this isn't always sufficient, depending on the nature of your systems & application. If you have the luxury, setting the services to start manually (or disabling them altogether) & rebooting is an often-chosen way to free up the resources (of course changing them back to the appropriate setting after defragmentation is complete)...as always, ensure that there are good backups to fall back on, just in case)
Before you begin, you may want to make note of filesystem fragmentation statistics, and keep that data on hand for reference (such data over time can allow you to determine how often such maintenance is necessary, if at all)
Depending on your chosen tool for defragmenting, you may have to defrag several times to achieve significant/desirable results.
I've seen a number of sql servers with very highly fragmented data files. They carried along merrily for months, sometimes years, growing and shrinking with day-to-day activity and maintenance with no other maintenance being conducted. For some such systems, the difference in performance resulting from this action alone was like night and day. Whether there's any value to defragmenting your log files will depend to a large degree on how logging is set up on the server.
-
1) you don't need AWE on a 4GB box. Investigate /3GB switch for you boot.ini in BOL.
2) If the database doesn't have lots of free space, index maint will not be able to defrag acceptably because there isn't enough contiguous empty space to lay the pages down in order. Since you have 390GB free space and a 10GB database, make the DB 50GB and then do the maint.
3) The optimizer will not use indexes if it needs to access more than a very low percentage of the total data in the table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru has made some good points. Expanding on 3:
- the optimizer will choose to use an index for a query if it is determined to be "selective enough"...that is to say if using the index is likely to isolate a small enough percentage of the table queried...you will see a variety of numbers thrown around regarding what is selective enough, with it suggested that you aim for an index to be able to isolate 5% of a tables population or less with the query thrown at it for which the index was designed (I believe 10% is the cutoff for the old optimizer, although I may be mistaken)
- that being said, it is possible for the optimizer to be fooled. To determine selectivity, it relies on the statistics for a given table -- depending on the index and query in question, it may appear to the optimization engine that an index is selective enough, when in fact it is not. Look at the statistics for the tables in question, and take time to understand both the queries that are running, and the indexes that may be used in that query's execution -- will those indexes be selective, given the data in question and the query thrown at it?
This reading may be of benefit on this matter:
http://www.sql-server-performance.com/indexes_not_equal.asp
http://www.sql-server-performance.com/tips/nonclustered_indexes_p1.aspx
http://www.sql-server-performance.com/articles/per/execution_plan_statistics_p1.aspx
and
http://www.databasejournal.com/features/mssql/article.php/1441301
-
If you can afford some read-only recordsets during the work day, then analysis services may be of interest. You can build your results during the night for the next day's use.
[font="Arial"]Clifton G. Collins III[/font]
-
Wrong... correctly formulated covering indexes will be used no matter how much of the correct columns are used... this example uses 90 % of the data in two columns and still produces an Index Seek. It won't be much faster than a table scan because of the amount of data selected, but still, it uses an Index Seek.
--===== 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% (~90% 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 <= 45000
--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)
Intro to Tally Tables and Functions -
1) Jeff, we have been through this before so no need to post your covering index proof. My statement is clearly for situations where the base table needs to be accessed for data.
2) The OP starts out with "I have a customer using SQL 2000 Developer Edition on W2003 Std....". Aside from the potential licensing issues of using dev ed for what seems to be production work, something tickles the back of my brain that there was a limitation to the number of connections available for this edition of sql server. I could well be misremembering though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Not sure what you're calling a base table but the correct covering indexes will allow for full length table access using index seeks across all tables in a join. By definition, a covering index should cover everything including what is selected from what I think you're calling a "base table". Please explain why you think I'm wrong and why you think you're right.
--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)
Intro to Tally Tables and Functions -
There are no technical limitations on the number of concurrent processes in the Developer edition. If the server is used in anyway by a "production" process, then there is license violations.
--------------------
Colt 45 - the original point and click interface
-
BTW, DDL for table and indexes, along with query and text execution plan are most helpful in providing a complete picture
--------------------
Colt 45 - the original point and click interface -
>> Not sure what you're calling a base table but the correct covering indexes will allow for full length table access using index seeks across all tables in a join. By definition, a covering index should cover everything including what is selected from what I think you're calling a "base table". Please explain why you think I'm wrong and why you think you're right.
Come on Jeff!! We had this exact same discussion before, as I mentioned. I never said you are wrong. Of COURSE a covering index will be used if available (even multiple indexes at once even if available) since it will cost less than accessing the base table using bookmark lookups or a scan of some sort. Base table is the actual table data. When I say sql server will not use an index when access to more than a very low percentage of the data is hit it means just that - the DATA must be accessed in the TABLE that stores it - not via one or more "covering" indexes. The cost of non-sequential I/O associated with bookmark lookups is just overwhelming when compared to the sequential I/O of a table scan which causes the optimizer to switch to a table scan well before most think it would.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply