SQL Server 2000 DateTime sorting issues

[szlamany]

Ok - so RESTORE the DB again - the slow one - onto the production server.

Do this:

exec sp_recompile SprocName

Then see if it runs "fast" on the first run.

If we changed the behavior of "first run" vs "subsequent" runs with this then we know that this SPROC falls prey to cached data access plans.

[Techno]

The initial message given from QA was that it successfully marked it for recompilation.

first run on the deployment server takes the same time as it always did... around 50 seconds.

executing it again, taking 21 seconds.

executing it again, took 4 seconds.

executing it with different parameters..... took 10 seconds.

now running it from the ASP.NET Website with the same parameters. timed out. probably because its using a different user account? Would it make a difference? I wouldn't have thought so but you never know.

[szlamany]

Ok - that's great - now we are on to something.

Do the

exec sp_recompile SprocName

and run the sproc

do the

exec sp_recompile SprocName

again and run the sproc

and do it one more time.

Does each of these three runs take around 50 seconds?

[sapator]

Hi.
Back.
So i see progress..
2 thinks.
Try to use convert on the datetime.I don't know what are your standards in UK.I use 13 or 113.
What collation do you have on your SQL server?Is it Standard Latin?

[sapator]

Also a personal question if you don't mind.
Are you working on your company on the Weekend?!
Here, maybe some hours on Saturday but on Sunday, only if the company had that new killer robot manager model X23

[Techno]

LOL @ saptor.
na, its just a client of mine. finished up the entire project but now need to deploy it and make sure it works just fine so they dont have to call me in future if the deployment didnt work. wanna make sure it works just fine. again, never had these problems before.

the collation is the default one that is used with SQL 2000

@szlamany - after rebooting the deployment server, which has the slow DB, it initially took..... 2m 14s.
doing it again took 1m 2s
doing it again took 1 second
doing it again took 1 second
doing it again with the parameter change took 1 second

when the server is not being used for 2 minutes and I re execute the query, it takes 58 secs

?!

[sapator]

Ah,ok.
Listen, did you try to convert datetime?
Also try to do a group by before order by, maybe we can fool the sql.

[Techno]

could do but we shouldnt really have to go to this extreme. something is causing SQL to go slow when doing a sort on datetime field.
I did try the convert datetime but no go
group by....still no go.

ive requested a proper physical computer this time to test it on, rather than the Virtual PC environment (deployment). Yes, more than sufficient memory has been allocated and a quad core CPU. VPC isnt slowing it down as we also verified this when I copied over the deployment database to the dev environment again on a new DB

[sapator]

I wasn't aware you have it on VPC.Yes maybe Virtual PC have some issues.
Tons of issues back with vs2005 at work.

[Techno]

I still dont think its a VPC issue. trust me on that. ive built many sites/databases and hosted them on VPC's as per client request but further more to try and eliminate the problem, copied over the DB from the VPC (deployment) to the dev computer (proper computer) and the issue still existed.

[sapator]

Ok one final thought.
Have you tried
SELECT *
FROM yourtable
OPTION (MAXDOP 1)

try to force a non-parallel plan ?

[Techno]

never seen that before my friend. ok... lets try
...
no go. :-/ took 55 seconds

[sapator]

Dammit
Ok, i'll be sure to check what is going on once i get my internet back.
Good luck again and good night.

[szlamany]

LOL @ saptor.
na, its just a client of mine. finished up the entire project but now need to deploy it and make sure it works just fine so they dont have to call me in future if the deployment didnt work. wanna make sure it works just fine. again, never had these problems before.

the collation is the default one that is used with SQL 2000

@szlamany - after rebooting the deployment server, which has the slow DB, it initially took..... 2m 14s.
doing it again took 1m 2s
doing it again took 1 second
doing it again took 1 second
doing it again with the parameter change took 1 second

when the server is not being used for 2 minutes and I re execute the query, it takes 58 secs

?!

Ok - I just have to be really clear here.

You did the SP_RECOMPILE between each of these steps and it STILL DID become faster with each run anyway.

But sitting idle for 2 minutes causes it to run slow again.

If this is the case then we can duplicate the following scenario.

Reboot
Run Query - Slow
SP_Recompile
Run Query - Fast
SP_Recompile
Wait 3 minutes
Run Query - Slow

and then with those numbers in mind do this:

Reboot
Run Query - Slow
Run Query - Fast
Run Query - Slow

If these produce exactly the same results then all talk of this being a sproc compilation issue can get tossed out the window - at least we would have one thing eliminated.

If the Wait 3 minutes step always produces a "slow again" query run then I would be curious to see if any "high volume" query would behave the same way.

I'm starting to think you have a bad install of SQL on the deployment server. Or it's got network issues that cause authentication of the connection to be delayed - couple that with connection pooling and maybe you are seeing the time of authentication getting involved when the "downtime" allows the pooled connections to expire in some way.

How are you authenticating on each of these boxes anyway? And how are you getting IIS up on both of these boxes? Explain the development box a bit...

[Techno]

Sure, thanks! Will try this and get back
How am I authenticating to the boxes? Well, the dev box is joined to the domain so im using domain credentials.
as for the deployment box, its just a standard Windows user account im using (Admin) but ASP.NET Authenticates to SQL via the network service user account (default account ASP.NET/IIS uses)

IIS is just simply installed through the control panel, as im sure you are aware of. Once done, I simply just create a virtual directory for the web app, copy over the files and job done. I'm able to then access the website.

Dev box has WS2008 x64 STD with all updates. Has VS2008 and SQL 2000 with all updates in place.

Deployment box has WS2003 STD with all updates and SQL 2000 with all updates (SP4)

never ever had a bad instance of SQL installation in my life but it is possible. will try a new install of the whole system just in case.

The Website and SQL Server will always remain on the same server and this is how the dev box is configured also.

[szlamany]

Oh - I didn't know the dev box was a server OS.

[Techno]

well any dev work I have done has ALWAYS been on a server OS. again, it shouldnt matter much on the OS really about how SQL should perform

[sapator]

Hi.
Back with inet but i don't know if they continue to have phone work on cable tomorrow.
Anyway, did you have any luck yet.
Some thoughts, some of them szlamany told.
SQL bad install issue.
If the problem only comes when datetime order by is on then you may want to order it by something else, or create another column.In the worst scenario have the datetime values in another table and do a join.Don't reconstruct anything, just put some values on a table a do an inner join from the query.Don't actually join them in the database.
Have you tried to do it on another instance of VPC and maybe on a new account with admin rights?
Also can you show me the connection string (remove passes,etc)?

[Techno]

yeh, the connection string is a normal one, nothing spectacular about it:

data source=(local);Initial Catalog=[dbname];Trusted_Connection=true;Application Name="MyWebApp"


thats it. doesnt matter if you replace (local) with "."

going to see what else can be done tomorrow when I visit the client hopefully. But it is a worry on why on earth it takes a long time on a datetime column to do a sort.

[leinad31]

Has the database instance been configured properly? Is there enough memory (DB cache being paged backed to file system by OS), more so for several running instances at dev?


Sidenote, dev and deployment environment should as similar as possible in order for you to make an apples to apples comparison and to facilitate proper simulation of issues. OS does matter as DB or OS might require additional patches. You haven't tried deploying to environment similar to dev (not in dev machine itself).

[Techno]

300MB has been assigned to the VPC. will try to assign more but dont think it will help much to be honest. everything is worth a try

[Techno]

wow
that worked. what the .......... ?!?!?$%$$$$$@@@@£$£@£$£@@

why on earth does it need more than 300MB to do a sort on datetime field?!
could also be a combination of rebooting the VPC image.

[sapator]

Well most of us were telling that VPC has some issues and reboot everything but...
Glad you solved the problem.Also there is a virtual control center,i cannot recall the name, that has lot's of issues.A friend of mine which is an admin is using it and i went to his home and he almost threw the pc at me.
Virtual...server...private,share something, i cannot recall.

[leinad31]

wow
that worked. what the .......... ?!?!?$%$$$$$@@@@£$£@£$£@@

why on earth does it need more than 300MB to do a sort on datetime field?!
could also be a combination of rebooting the VPC image.

More often than not, greatest performance hindrance of database (any database, oracle, sql server, mysql, etc) is disk I/O. In order to handle volume of data involved (note that additional copies of data generated for read consistency and undo), if all read pages doesn't fit in memory then depending on settings it can be persisted back to disk via system or user tablespaces.. unlike cache/memory access it was retrieved from disk, persisted back to disk (temp/sort store), then retrieved again from disk hence increase in delay and variable duration (depends on current load on disk). Also as much as possible it should be actual/hardware memory, not additional memory via OS page files.

[Techno]

but what didnt make sense was the fact that after restoring the DB on the VPC image, then backing that up and restoring it onto the main PC Again.... it was still showing the problems. the main PC has 2GB of mem

[GaryMazzone]

Our Virtual DB Servers run on a phyiscal machine with at least 32GB of memory... We will normallay make 2 virtual machines (sometimes 3) from that. With a min of 16GB for database servers.

[Techno]

yeh sounds about right. something ive said to the client that make sure the server has at least a min of 10GB as i dont think they would be getting many visitors initially.

[leinad31]

but what didnt make sense was the fact that after restoring the DB on the VPC image, then backing that up and restoring it onto the main PC Again.... it was still showing the problems. the main PC has 2GB of mem

So you overwrote or stopped original dev instance to run 2nd copy on dev server? If they were running concurrently then they will compete for resources.

Also how was storage layed out? Separate disk controllers to prevent bottleneck? Increasing memory (and database cache) is the easier hardware tune so make sure you can scale that out since rearchitecting storage access later on would be difficult and may require that the database be taken offline.

[Techno]

I stopped the original DB on the dev machine where the DB was copied from the VPC back to the dev (only me accessing the site, its not live yet) and still caused the problem

as for the storage layout - its just a simple DEV box (2GB RAM, SATA HDD, dual core CPU). But like I said, the system works fine on the dev box without the issues that are faced when the DB is copied to the VPC image, which is running on that same dev box