April 16, 2002 at 6:25 pm
Ok, here's the problem, i have a database which is on our development server, SQL2000, and it works fine.
bottomitems (Records=6, Time=10ms)
SQL =
SELECT *
FROM [237_1]
WHERE parentid < 1
AND status <> 2
AND active = 1
AND image = 'bottom'
ORDER BY sortorder, label
One of our hosted server is SQL 7 unfortunately, and when i do exactly the same query on it, i get this
getstuff (Records=6, Time=94ms)
SQL =
SELECT *
FROM [237_1]
WHERE parentid < 1
AND status <> 2
AND active = 1
AND image = 'bottom'
ORDER BY sortorder, label
and that goes up to 500ms sometimes
I can't seem to find out where the bottleneck is, can anyone give me some ideas on what to check, what to tweak and what to look at to get it running smoothly?
Thanks
April 16, 2002 at 7:43 pm
Well to it's bennefit SQL2000 does have some engine enhancements. However run in QA with show exectuion plans on to see if there is any difference between the twos plans. Could be any number of issues but first place is indexes and the ways statistics are updating. Also how are you making sure the 7 server is keeping indexes and statistics on those (SQL 7 did have soem issues along those lines)? Also what are the hardware differences and number of records.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 16, 2002 at 8:09 pm
When i run the query on the SQL 7 box in QA it says that it's missing the statistics for this table and to right click on it, and choose create missing statistics, although when i do that it doesn't seem to make any difference in speed
there's 46 rows in the table called "[237_1]"
SQL 7 the execution plan shows
SELECT 0%, Compute Scalar 0%, Sort 23%, Filter 0%, 237_1.pk_237_1 77%
Where as the SQL 2k box does this
SELECT 0%, Compute Scalar 0%, Sort 23%, 237_1.pk_237_1 77%
SQL 7 is a dual p3 933, 1GB of RAM running win2k with SCSI RAID 5
SQL 2k is a p3 1GHz 512MB RAM running win2k
maybe it's the clustered index screwing it up? all i did was create a sql script in sql 2k (making sure it only used sql 7 commands) ran that, creating the db, then imported all the data
April 17, 2002 at 5:00 am
I would also be curious to know why you have the extra Filter occurring. Just out of curiosity, you stated the 2000 box was dev, copy the DDL from EM for the 7 box and create the table on the 2000 box (new DB that you don't have to interfere with the other). Now import the data to it and run the query there to see if it then repeats the same thing as the 7 box. Also can you post the DDL from both the 2000 and 7 boxes that you currently have, so I can look at their structures and have a way to test here.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 17, 2002 at 5:08 am
If you can, detach the db and copy/attach to the SQL2K box, that we you know for sure that everything is exactly the same. I'd run sp_updatestats on it after attaching just to be sure, then go from there.
Andy
April 18, 2002 at 2:43 am
ok, i decided it was about time to upgrade the hosting box to sql2k sp2, so that's what i've just spent the last 2 hours doing *sigh*
Although it has helped the performance, it still isn't as good as our local db server
I backed up the db locally and transfered it to the hosting db server where i restored it, meaning it should run the same right?
So what i can look at now to speed it up? It's no longer putting that filtering in there, so that's a bonus
April 18, 2002 at 4:58 am
Ok now it gets fun. I have a suspect on one reason but need some more details here. First what is the time difference now? Also can you post the DDL of the table from both servers (just want to compare)? And lastly using the following syntax
SET SHOWPLAN_TEXT ON
GO
yourqueryhere
GO
SET SHOWPLAN_TEXT OFF
GO
can you post the output from doing this on both servers? These shoul help me answer the question.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 18, 2002 at 5:13 am
Ususally culprit is bad statistics, is not stated very well in the upgrade docs - need to rebuild the stats to get good query plans. Stats changed from 7 to 2000.
Andy
April 18, 2002 at 3:28 pm
i hope this is what you mean:
development (the one that works):
CREATE TABLE [237_1] (
[pageid] [int] IDENTITY (1000, 1) NOT NULL ,
[HTMLtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[languageid] [int] NOT NULL CONSTRAINT [DF__237_1__languagei__2665ABE1] DEFAULT (1),
[parentid] [int] NOT NULL CONSTRAINT [DF__237_1__parentid__2759D01A] DEFAULT (0),
[active] [smallint] NULL CONSTRAINT [DF__237_1__active__284DF453] DEFAULT (0),
[createdate] [datetime] NULL CONSTRAINT [DF__237_1__createdat__2942188C] DEFAULT (getdate()),
[modifydate] [datetime] NULL CONSTRAINT [DF__237_1__modifydat__2A363CC5] DEFAULT (getdate()),
[modifyuser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[templateid] [int] NOT NULL CONSTRAINT [DF__237_1__templatei__2B2A60FE] DEFAULT (1),
[label] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shared] [bit] NOT NULL CONSTRAINT [DF__237_1__shared__2C1E8537] DEFAULT (0),
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sortorder] [int] NULL CONSTRAINT [DF__237_1__sortorder__2D12A970] DEFAULT (0),
[title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [smallint] NULL CONSTRAINT [DF__237_1__status__2E06CDA9] DEFAULT (1),
[thestartdate] [datetime] NULL CONSTRAINT [DF__237_1__thestartd__2EFAF1E2] DEFAULT (getdate()),
[comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[siteid] [int] NULL ,
[objectid] [int] NULL ,
[display] [bit] NOT NULL CONSTRAINT [DF__237_1__display__2FEF161B] DEFAULT (1),
[image] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__237_1__image__31D75E8D] DEFAULT (''),
[defaultlang] [int] NOT NULL CONSTRAINT [DF__237_1__defaultla__32CB82C6] DEFAULT (1),
[hitcount] [int] NOT NULL CONSTRAINT [DF__237_1__hitcount__33BFA6FF] DEFAULT (0),
[moduleData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[linktarget] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_237_1] PRIMARY KEY CLUSTERED
(
[pageid]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
production (the recently upgraded one):
CREATE TABLE [237_1] (
[pageid] [int] IDENTITY (1, 1) NOT NULL ,
[HTMLtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[languageid] [int] NOT NULL CONSTRAINT [DF__237_1__languagei__2665ABE1] DEFAULT (1),
[parentid] [int] NOT NULL CONSTRAINT [DF__237_1__parentid__2759D01A] DEFAULT (0),
[active] [smallint] NULL CONSTRAINT [DF__237_1__active__284DF453] DEFAULT (0),
[createdate] [datetime] NULL CONSTRAINT [DF__237_1__createdat__2942188C] DEFAULT (getdate()),
[modifydate] [datetime] NULL CONSTRAINT [DF__237_1__modifydat__2A363CC5] DEFAULT (getdate()),
[modifyuser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[templateid] [int] NOT NULL CONSTRAINT [DF__237_1__templatei__2B2A60FE] DEFAULT (1),
[label] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shared] [bit] NOT NULL CONSTRAINT [DF__237_1__shared__2C1E8537] DEFAULT (0),
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sortorder] [int] NULL CONSTRAINT [DF__237_1__sortorder__2D12A970] DEFAULT (0),
[title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [smallint] NULL CONSTRAINT [DF__237_1__status__2E06CDA9] DEFAULT (1),
[thestartdate] [datetime] NULL CONSTRAINT [DF__237_1__thestartd__2EFAF1E2] DEFAULT (getdate()),
[comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[siteid] [int] NULL ,
[objectid] [int] NULL ,
[display] [bit] NOT NULL CONSTRAINT [DF__237_1__display__2FEF161B] DEFAULT (1),
[image] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__237_1__image__31D75E8D] DEFAULT (''),
[defaultlang] [int] NOT NULL CONSTRAINT [DF__237_1__defaultla__32CB82C6] DEFAULT (1),
[hitcount] [int] NOT NULL CONSTRAINT [DF__237_1__hitcount__33BFA6FF] DEFAULT (0),
[moduleData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[linktarget] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_237_1] PRIMARY KEY CLUSTERED
(
[pageid]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Timing:
The production server is taking between 78ms and 560ms to process that query, which is a slight improvement
The development server is about 10ms
As far as i can tell they're the exactly the same database
If it is a problem with the stats, how do i go about recreating them?
Thanks for you help guys
April 18, 2002 at 3:58 pm
Run sp_updatestats. SQL keeps track of the data distribution to help it determine what the best query plan will be. If the statistics are out of date, you'll get query plans that are less effective. The default in SQL is to have the statistics updated automatically and they also get updated whenever an index is rebuilt. sp_updatestats does it for everything in the db, a good way to rule out a problem. In the case of the upgrade to SQL2K, it's a critical step to leverage the changes.
Andy
April 18, 2002 at 4:12 pm
I couldn't do a direct upgrade from 7 -> 2k, i had some problems, so i uninstalled the mess that the installer left and installed 2k from scratch
The database in question was moved from one sql2k server to another, i can't understand how it would be any different being that the production sql server is twice as powerful and is running a RAID 5 array
but yes, i had already run sp_updatestats (found it in the sql upgrade guide and thought it might help), but it hasn't helped any 🙁
April 18, 2002 at 8:00 pm
You're back to profiling. Only way to see for sure it to see the query plan plus the io and cpu usage.
Andy
April 18, 2002 at 8:42 pm
here's the results of what Antares686 suggested i do, they're exactly the same
SET SHOWPLAN_TEXT ON
GO
SELECT *
FROM [237_1]
WHERE parentid < 1
AND status <> 2
AND active = 1
AND image = 'bottom'
ORDER BY sortorder, label
GO
SET SHOWPLAN_TEXT OFF
GO
production:
SELECT * FROM [237_1] WHERE parentid < 1 AND status <> 2 AND active = 1 AND image = 'bottom' ORDER BY sortorder, label
|--Compute Scalar(DEFINE:([237_1].[moduleData]=[237_1].[moduleData], [237_1].[comment]=[237_1].[comment], [237_1].[description]=[237_1].[description], [237_1].[HTMLtext]=[237_1].[HTMLtext]))
|--Sort(ORDER BY:([237_1].[sortorder] ASC, [237_1].[label] ASC))
|--Clustered Index Scan(OBJECT:([ddcgaga].[dbo].[237_1].[PK_237_1]), WHERE:((([237_1].[parentid]<Convert([@1]) AND [237_1].[status]<>Convert([@2])) AND [237_1].[active]=Convert([@3])) AND [237_1].[image]=[@4]))
development:
SELECT * FROM [237_1] WHERE parentid < 1 AND status <> 2 AND active = 1 AND image = 'bottom' ORDER BY sortorder, label
|--Compute Scalar(DEFINE:([237_1].[moduleData]=[237_1].[moduleData], [237_1].[comment]=[237_1].[comment], [237_1].[description]=[237_1].[description], [237_1].[HTMLtext]=[237_1].[HTMLtext]))
|--Sort(ORDER BY:([237_1].[sortorder] ASC, [237_1].[label] ASC))
|--Clustered Index Scan(OBJECT:([ddcgaga].[dbo].[237_1].[PK_237_1]), WHERE:((([237_1].[parentid]<Convert([@1]) AND [237_1].[status]<>Convert([@2])) AND [237_1].[active]=Convert([@3])) AND [237_1].[image]=[@4]))
What can i do to get io and cpu stats for the query?
April 19, 2002 at 4:47 am
A simple way in QA is to click query, server trace. That will give you both numbers.
Andy
April 19, 2002 at 5:02 am
ok, here we go
production:
SQL:StmtCompleted
Duration 242
CPU0
Reads 53
Writes 0
development:
SQL:StmtCompleted
Duration 80
CPU 0
Reads 53
Writes 0
i'm accessing both from home so i don't know if that'll make a difference (dev box is in the office, production box is in the states)
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply