SQL Server 2000 DateTime sorting issues

[Techno]

This is very odd behaviour.

we have a large database somewhat (around 6GB) of data.
Now, on the dev box, we can execute SPROCS called from .NET or QA, which involves sorting the TOP 5 records by datetime field. This works pretty much immediately giving me back the result.

When using the SAME database but on a fresh and new environment, it takes over 5 minutes or just under for it to give back the results!

After commenting out the sort field for datetime, the problem has gone away and data comes back fast.

Why such a behaviour on a new fresh system using the same SPROCS and parameters??

how can I resolve the performance issue as some data needs to be sorted at the SQL end on the datetime columns.

any tips/tricks/advice?

[techgnome]

Drop and re-create the sproc, or recompile it, or update your statistics. We just ran into a similar problem. There wasn't a problem with the query itself, but the cached execution plan was no longer valid as the data had changed over time. Purely by accident, I found dropping the sproc and recreating it allowed a new execution plan to be created based on the new data. We went from an sproc that timed out after 5 minutes, to completion in 3 seconds.

-tg

[Techno]

there are over 100 SPROCS. and this is also on a new system altogether.... so it makes no sense on why its doing it. but then what about the future? say maybe in 5 months time, would I then have to drop and recreate? shouldnt have to....

[Techno]

i tried that but still no go matey. still the same. deleted the sprocs, restarted the SQL instance service, and then recreated the SPROCS.... still no go

[GaryMazzone]

Have you looked at the execution plan from both systems? are they the same? Is degree of parallelism different between the systems?

[Techno]

the degree of parallelism is the same.
a few days again something similar like this also happened involving datetime.
.NET app was passing the datetime value as expected, same to the other SPROCS and was working fine up until this specific SPROC was calling another SPROC inside and it just blew up SQL.

After formatting the datetime format from .NET to be yyyy/mm/dd it was working fine which was very odd as it has been working fine by default with other SPROCS.

other thing I also noticed is that when we are say, concatinating strings in SQL, it used to allow you to do this:

DECLARE myString nvarchar(500)
SET myString = 'hi'

but for whatever reason it didnt do that this time but if you initialise it with a default value (empty string), then sets the value correctly thereafter


there seems to be alot of problems all of a sudden and no idea how or where. no one has modified the SQL instances in any way.

will check on the execution plan. is almost instant on the original DB..... but now waiting for it to "complete", whenever it does, on the deployed server.

[GaryMazzone]

If parallelism is 0 or greater the 1 maybe try and set to 1 on the server and see if that would help

[Techno]

execution plan is pretty much the same my friend.
on the deployed site, it takes 33seconds + to execute the sproc. on the dev server, about 1 second. the stats seems to come back the same.

[Techno]

ok i tried changing the degree of parallelism to 1. still no go im afraid. maybe shaving off 4 seconds from 36 but still too long and it times out from the .NET app.

[sapator]

A long shot but.
Did you deploy it on 1 machine or it does the same with more?
Have a look at the network wiring if you can.
(if it only does that on one machine, use gasoline and a lighter ).

[Techno]

lol. indeed, gasoline and lighter is the next step.

only deployed it on 1 machine. rebuilding the machine again or using Virtual PC still makes no difference.
Network connection is all fine as its running the ASP.NET Application on the same server as the SQL Database (so connection string is local)

just makes no sense why on earth it times out for doing a simple query:

SELECT TOP 6 [5 fields here] FROM Accounts a
INNER JOIN Pubs p ON
p.AccountNo = a.AccountNo
WHERE a.AccountNo = @param1 OR a.AccountNo = @param2 OR a.AccountNo = @param3 AND p.PubStatus <> 'dead'
ORDER BY p.AmendedDate DESC


simple query.
I remember it used to time out on the dev environment (same DB) but this was due to the transaction log being over 2GB but that was easily fixed. The same fix is for the database on the deployed server (transaction logs are 10MB)


How did I deploy the DB? :

Backup from original DB
Restore on the deployed server. thats all!

[sapator]

Hmm.
Another thought would be (if you haven't done it already) to use trace to see if there is something else holding the transactions.
Also have you tried to re-create the db and not copy it?

[Techno]

nothing is holding the transactions
recreating the DB? could do but the problem is - there is existing data (3GB worth) which is required.....

[sapator]

Ye, i'm talking about take a small sample data.You are only selecting 6 rows anyway.

Also you are using the db from SQL or VB?
Try both.If vb do the slow then the problem is on vb code.
Using DAL or simple ado.net?

[Techno]

meh, true. thing is, I didn't design the DB or data..... and there are alot of tables.

but yeh, will try. May even recreate the DB with empty tables, then import the data in. But still, I would like to get to the bottom of why this is happening.

[techgnome]

Just for giggles, try this:

SELECT TOP 6 [5 fields here] FROM Accounts a WITH (NOLOCK)
INNER JOIN Pubs p  WITH (NOLOCK) ON
p.AccountNo = a.AccountNo
WHERE a.AccountNo = @param1 OR a.AccountNo = @param2 OR a.AccountNo = @param3 AND p.PubStatus <> 'dead'
ORDER BY p.AmendedDate DESC

Might also want to think about some parenthesis around that where clause... is it param 1 or param 2, or param 3 AND not dead.... or is is param 1, or 3, or 3..... AND not dead...

In other words:

WHERE ((a.AccountNo = @param1) OR (a.AccountNo = @param2) OR (a.AccountNo = @param3)) AND p.PubStatus <> 'dead'

or

WHERE ((a.AccountNo = @param1) OR (a.AccountNo = @param2)) OR ((a.AccountNo = @param3) AND (p.PubStatus <> 'dead'))

-tg

[Techno]

this is the actual query my friend:

SELECT TOP 5  app.ApplicationKey, app.ApplicationName, app.AppType, app.WebPubStatus, app.ApplicationNo, app.AmendedDateTime, app.AccountNo, app.CustomerCode,
app.DespatchInst, app.LastOrdered, app.OrderRecvdDate, app.ProductType, app.ApplicationStatus, app.RoyaltyPrice, app.[Selling Price], app.UnitPrice, app.VersionNo
FROM Applications app
WHERE (app.ApplicationStatus <> 'dead' AND app.TempOrPub = 'P' AND app.ProductType = 'N' AND app.AccountNo > 0)
AND (app.AccountNo = 16101 OR app.AccountNo = 16100 OR app.AccountNo = 0
OR app.AccountNo = 0 OR app.AccountNo = 0)

ORDER BY app.AmendedDateTime DESC

[sapator]

What troubles me is that you didn't fiddle with the database at all.
So have a try to call the query from SQL and then from vb and see if it has the same time response.
Also,(although the transaction is internal but...), try to use a different net plug.

[sapator]

Eh.Only one table call?Sorry this table has 3G data or all the database?

[Techno]

all the DB has around 3-4GB.
the same problem happens calling it from a .NET app or from within the QA (Query Analyser). Eventually the QA does complete (after 40 seconds ish) but .NET app times out. Even setting the CommandTimeout to say 160 secs still results in it timing out and im pasting the exact query that is being executed that profiler sees when .NET calls the SPROC and passes in the parameters.

[sapator]

So, to get things straight.
You had a db, duplicated it and the new db will stall a lot.
The same query will execute immediately in the first db but will stall in the second one?
You're certain that the environment is the same,or better?
The vb code is the same.

What i would have done:
Create a copy of a fresh new one from the original.
Let it on the exact same folder as the old one.
Restart ALL the systems.DB server,server,reset everything.
Only test the query in SQL and make sure noone "plays" with the db.
If someone "plays" shoot him :P

After all that and AFTER testing try to compact the NEW database.

[sapator]

Note that i haven't yet suggested to drop all the indexes and recreate.
That's i what the company responsible for my old company's db was doing but this is a final measure and suggests that something has changed in the db first.

[sapator]

Have to hit the sack.Good luck.

[Techno]

thanks sapator! Will let you know what the outcome is.
ive just recreated the entire DB with empty data. Now in the middle of copying over all the data to the new recreated database

Environment the same? yes, except the OS - the OS is WS2003R2, the dev environment is WS2008R2 but that shouldn't matter really.

lets see how it goes....

[Techno]

ok.

I scripted the database to create users/tables/sprocs
I created a new database
I executed the script so it creates the database schema etc... etc...

I then imported the data into the new database from the "dev" database

guess what?

problem STILL Exists. This is not good at all.

[sapator]

Yes it's not good
But if your initial db got the query right then a copy or better yet the whole db should do it correct.
I think there must be something else, network wise.
Btw tomorrow my internet goes off so i won't be able to help.

[Techno]

thanks, i appreciate everything.

but the thing is, it has nothing to do with the network connection as the database is local to the one where the website is....

[szlamany]

this is the actual query my friend:

SELECT TOP 5  app.ApplicationKey
, app.ApplicationName
, app.AppType
, app.WebPubStatus
, app.ApplicationNo
, app.AmendedDateTime
, app.AccountNo
, app.CustomerCode,
app.DespatchInst
, app.LastOrdered
, app.OrderRecvdDate
, app.ProductType
, app.ApplicationStatus
, app.RoyaltyPrice
, app.[Selling Price]
, app.UnitPrice
, app.VersionNo
FROM Applications app
WHERE (app.ApplicationStatus <> 'dead' 
AND app.TempOrPub = 'P' 
AND app.ProductType = 'N'
AND app.AccountNo > 0)
AND (app.AccountNo = 16101 
OR app.AccountNo = 16100 
OR app.AccountNo = 0
OR app.AccountNo = 0 
OR app.AccountNo = 0)
ORDER BY app.AmendedDateTime DESC

So - this is the query - correct?

Let's start cutting parts off of it to get it down to the most simple query that still takes a long time to run.

For example - let's get that SELECT list down to one item. Does that still take a long time to run?

Then let's start removing those OR's - they obviously create issues in the WHERE clause.

I'm concerned that the database engine might be making your entire resultset and THEN taking the top x items off of that.

Have you told us how many rows are in the table itself? I understand that the DB itself is large - and of course the disk placement of the data can become meaningful.

Have you tried creating a DB with just this one table and doing tests against that?

My attack would have been slightly different - I would make a staging temp table with the PK's and the "sort value" and then take off of that.

[Techno]

sure my friend. ok let me give you some interesting stats.
Firstly, if it works fine on the dev environment, why not the deployed environment? Same thing except different OS. the OS shouldnt make a difference. ive never had such issues before and seen some more complicated queries before!


Deployed server when executing that query:

CPU: 20 Reads: 596 Writes: 0 Duration: 400

Dev server when executing that query:

Reads: 137
Writes: 0
Duration: 16

For example - let's get that SELECT list down to one item. Does that still take a long time to run?

Yes

Then let's start removing those OR's - they obviously create issues in the WHERE clause

Still the same my friend. This is WITH just 1 field returning.

The account table has: 1005 rows. doing a SELECT * FROM Accounts completes in 2 seconds.
The Applications table has 143329 rows.

Have you tried creating a DB with just this one table and doing tests against that?

yes, still the same

[szlamany]

But the ACCOUNTS table is not in this query - right? I see just APPLICATIONS.

And 100,000 rows is a truly small number when we are talking about MS SQL performance.

What are the physical stats on the two disks that these DB's reside on?

There is a way to find out the "physical disk space" taken up by a table - let me see if I can find that. Also I'm wondering about "fill percentage" related to how much of the 8000 byte disk buckets are taken up by active data vs free space for expansion.

[Techno]

accounts table is in the query also my friend, as is the applications table

the dev server has 230GB HDD of which 86.4GB is free
the deployment server has 15GB of seperate disk space for the DB only of which 9 GB is free

[szlamany]

accounts table is in the query also my friend, as is the applications table

Where?

SELECT TOP 5  app.ApplicationKey
, app.ApplicationName
.
.
.
, app.VersionNo
FROM Applications app
WHERE (app.ApplicationStatus <> 'dead' 
AND app.TempOrPub = 'P' 
.
.
.
ORDER BY app.AmendedDateTime DESC

[Techno]

edit: double post
edit: Sorry, you are correct. I was thinking there was an accounts join... there isnt.

[szlamany]

So show me the "smallest" query you have right now that still runs slow - you said it's down to one field in the SELECT list and one where clause "condition"

I've got a trick up my sleeve that I would like to try but I need to know exactly where you are at right now with the simple query.

[Techno]

interesting. its a DateTime problem!

when doing the sort on the datetime column, it is where its holding up everything. if I take out the sort, the entire query runs as fast as I expect it to - no problems there.
but we have other SPROCS which are needing to do some datetime things including SPROCS running other SPROCS......


so for now, this seems to be the culprit.

even if the simplest query was to select 1 field where the account number was of a value and INCLUDING this datetime sorting order.... it still slows it down. taking the order by out.... is fine.
doing an order by another non datetime field...is fine also. but doing an order by on the datetime field....causes this issue

[szlamany]

Well - my trick was going to be putting an ALTERNATE key of the DATETIME field COMBINED with the PK of the table itself.

So it would be a compound alternate key.

And it's even better if you could take some other fields that are in the SELECT list and make them part of the ALTERNATE KEY. By doing that you allow a lot of the query pre-processing to be in the key and not the data portion of the table.

It's actually possible to take all the fields of a SELECT list an make them "additional" members of an INDEX - thus getting 100% of a query to be processed out of the index leaves and never needing to go to the data blocks of a table.

Disk space is cheap now - making these options all the more appealing.

[Techno]

can you provide me an example syntax of the suggested solution if you dont mind?

but still, why does it work "fine" on the dev server and not the deployed server?
why is the datetime field causing the hassle? I know its a more expensive field to sort on but.... surely not for 5 records of which around 3000 exist for that particular account

[szlamany]

Alternate keys are made like this:

CREATE INDEX AKClass on Class_T (Yr,Bldg,Crs,Sectn,Timevec)

I'm curious if you use BACKUP to take the "slow running DB" from the production server...

Then RESTORE that on the development machine that seems to run this query fast.

If that query will still run fast against this "restored" "slow running DB".

[Techno]

ill try that and get back soon as.... thanks!

[Techno]

ok.
I backed up the "slow" DB and restored it on the dev server.
running the same query - same problem BUT only on the first execution, thereafter its fine even when changing parameters!

running the same query but on the "normal" DB on the dev, no problem (works fine) (even with changing parameters)