SQL 2000 performance problem

[rockdays]

Hi,

I have a table above 8.000.000 records. I am running a query like
"select * from table where id=1093203". And the query turns in 24 second. This example i tested in SQL Server 2000 v8, Windows 2003 sp2.

I am running same query in Windows Xp + Sql server 2005 platform. The query returns about 2 second.

The first platform which i explained top is better hardware than second one. Also there isnt any cpu or ram overload problem. Everything seems ok. But i dont understand what is the problem. Could you help me to solve this?

[Ramy Mahrous]

Yes, SQL Server 2005 has better performance over SQL Server 2000, and I think migration to 2005 is essentially.
No problem from your side else remove * and replace it with columns names. Yes it makes sense.

[rockdays]

Yes, SQL Server 2005 has better performance over SQL Server 2000, and I think migration to 2005 is essentially.
No problem from your side else remove * and replace it with columns names. Yes it makes sense.

I am getting single column but no sense. Is it only about Sql server version?

[Ramy Mahrous]

Yes

[sknake]

For a single record I do not think that the SQL Server Version and hardware explain the difference entirely. Indexes become fragmented over time and need to be rebuilt. When was the last time you have ran any form of database maintenance on it? Also there are online ways to rebuild an index that are not as comprehensive as offline methods.

Post the output for a dbcc showcontig on the table from SQL2000 and SQL2005 here, I highly suspect that you will see a difference in fragmentation. Also post the table structure and index structure here, you should be able to generate that by right clicking the clipboard -- create to -- clipboard

Also for running the test on your SQL2000 machine I assume you are using the live/production database, and depending on how your indexes are configured you could be having a locking issue in bringing the record forward. Re-run the test on your 2000 machine with: select * from table (nolock) where id = 123456677

[rockdays]

For a single record I do not think that the SQL Server Version and hardware explain the difference entirely. Indexes become fragmented over time and need to be rebuilt. When was the last time you have ran any form of database maintenance on it? Also there are online ways to rebuild an index that are not as comprehensive as offline methods.

Post the output for a dbcc showcontig on the table from SQL2000 and SQL2005 here, I highly suspect that you will see a difference in fragmentation. Also post the table structure and index structure here, you should be able to generate that by right clicking the clipboard -- create to -- clipboard

Also for running the test on your SQL2000 machine I assume you are using the live/production database, and depending on how your indexes are configured you could be having a locking issue in bringing the record forward. Re-run the test on your 2000 machine with: select * from table (nolock) where id = 123456677

I tried nolock but nothing changed.

I did the test. You can see it below.
Rows count are diffrent. Cause i downloaded the database localy and the data is growing time by time.

USE [****dbname]
GO
/****** Object: Table [dbo].[DATAS] Script Date: 02/25/2009 10:38:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DATAS](
[SERIALNUM] [nvarchar](15) NULL,
[PRODUCTCOD] [nvarchar](9) NULL,
[PRODUCTION] [float] NULL,
[TIME] [float] NULL,
[DATESTAMP] [float] NULL,
[STARTDATE] [nvarchar](8) NULL
) ON [PRIMARY]

SQL SERVER 2000 RESULTS

ROWS COUNT : 8820868

DBCC SHOWCONTIG scanning 'DATAS' table...
Table: 'DATAS' (1426104121); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 103775
- Extents Scanned..............................: 12993
- Extent Switches..............................: 12992
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.84% [12972:12993]
- Extent Scan Fragmentation ...................: 0.11%
- Avg. Bytes Free per Page.....................: 21.0
- Avg. Page Density (full).....................: 99.74%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--------------------------------------------------------------------------------------------

SQL SERVER 2005 RESULTS

ROWS COUNT : 8725976

DBCC SHOWCONTIG scanning 'DATAS' table...
Table: 'DATAS' (1426104121); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 102685
- Extents Scanned..............................: 12840
- Extent Switches..............................: 12839
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.97% [12836:12840]
- Extent Scan Fragmentation ...................: 9.54%
- Avg. Bytes Free per Page.....................: 361.4
- Avg. Page Density (full).....................: 95.54%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[rockdays]

Ups, i realized that i need to make a defragmentation. Is it true? I need your comment about this.

[sknake]

Yes, it is true that this should be done periodically. Can you also post your index structure, it seems I was mistaken in thinking indexes were copied out in the create to statement but they are important.

Here are some queries you can use for maintainenance to see if you can pick up the performance. DO NOT RUN THESE QUERIES DURING HIGH USAGE TIMES. This is going to make the SQL Server slow until they finish.

dbcc dbreindex('DATAS', '', 80)
GO
UPDATE STATISTICS DATAS WITH FULLSCAN
GO
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS
DBCC CHECKDB WITH NO_INFOMSGS

[rockdays]

Yes, it is true that this should be done periodically. Can you also post your index structure, it seems I was mistaken in thinking indexes were copied out in the create to statement but they are important.

Here are some queries you can use for maintainenance to see if you can pick up the performance. DO NOT RUN THESE QUERIES DURING HIGH USAGE TIMES. This is going to make the SQL Server slow until they finish.

dbcc dbreindex('DATAS', '', 80)
GO
UPDATE STATISTICS DATAS WITH FULLSCAN
GO
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS
DBCC CHECKDB WITH NO_INFOMSGS

Thanks for your help. But i havent use any index. What you mean about index structure? I havent any primary key or autoincrement field. Could you explain mi what i need to do? I am goint to use this commands but first i need to know about indexes. Waiting for your help. Thanks again.

[sknake]

Well that solves your problem, you really really really really HAVE to have indexes on large tables such as that.

CREATE NONCLUSTERED INDEX IX_DATAS_3 on dbo.DATAS
(
  SerialNum
)

The name "IX_DATAS_3" can be changed to anything, that is the default naming scheme. After you create the index, which will take some time, your query will run every time in less than second.

[rockdays]

Well that solves your problem, you really really really really HAVE to have indexes on large tables such as that.

CREATE NONCLUSTERED INDEX IX_DATAS_3 on dbo.DATAS
(
  SerialNum
)

The name "IX_DATAS_3" can be changed to anything, that is the default naming scheme. After you create the index, which will take some time, your query will run every time in less than second.

I will try this. Hope this solve the problem:) Thanks for your great help. I have few questions:
Will i use the index name "IX_DATAS_3" in my sql query? Or its only an index name, it should be created?

[rockdays]

Also which field i need to use as index? I have a several query that brings diffrent field values. And use inner join with this table.

[sknake]

You should index only the fields you search on with a query. If you search on a serialnumber then index that field, so I cannot answer that question you need to determine what makes sense. On a side indexing is a balancing act. The more indexes you have then the longer it takes to insert records because it has to insert the data in each index as well, so don't go overboard with indexing.

The naming convention for indexes is
First Index: IX_TableName_1
Second Index: IX_TableName_2
Third Index: IX_TableName_3

I picked _3 in case you had an index so it wouldn't give you an error with the name. You can create indexes through management studio by right clicking on the table, click design, then right click on the column list and choose "Indexes/Keys" and click "Add". That will help you build an index.

[rockdays]

You should index only the fields you search on with a query. If you search on a serialnumber then index that field, so I cannot answer that question you need to determine what makes sense. On a side indexing is a balancing act. The more indexes you have then the longer it takes to insert records because it has to insert the data in each index as well, so don't go overboard with indexing.

The naming convention for indexes is
First Index: IX_TableName_1
Second Index: IX_TableName_2
Third Index: IX_TableName_3

I picked _3 in case you had an index so it wouldn't give you an error with the name. You can create indexes through management studio by right clicking on the table, click design, then right click on the column list and choose "Indexes/Keys" and click "Add". That will help you build an index.

You are great!

Thanks for your help. It solved my problem! Query is workinng very quick now!

Thanks again..

[sknake]

I'm glad the solution worked for you. Will you please mark this thread as solved?

Thanks

[Ramy Mahrous]

And He should have reputation points as well :)

[sknake]

And He should have reputation points as well :)

Thanks Ramy ;)