SQL1 running like a dog

yea have used IP and names.

it wasnt a problem on SQL3 for weeks and weeks (From sign up)... it all worked fine. Then one day, without me making any changes, it all fell to pot.

So u moved me to SQL1, which seemed to be ok and running very smooth so far.... and hasnt had a single problem stephen since u moved the DB for me. But, the last week has been up and down, and i havent changed my code at all. How can it be my code if 1 minute it works fine then the next its crap???

Im now thinking that it might be an issue with indexing or something??? Im running traces on my test server to compile a report of what transactions are carried out the most. Then im going to create and SQL script to generate indexes that will speed up performace. I will run this script on my test server and monitor the difference in performance. If successful, i will do the same on SQL1.

Wish me luck!
 
I am not seeing much CPU intensive running on SQL1, I have been monitoring it throughout the day, whatever you find would be helpful.
 
ok, ive not much idea wot im doin with indexes, but ive created crap loads of them.

Guess what?

"Page generated in 0.5094 seconds" !!!!!! :D :]

Im rather chuffed now.

Will still monitor though. Fingers crossed still for me. Thank you for your help folks.
 
The changes i made last night cocked it all up, couldnt post anything.
SO, ive reverted back and ive done some more work now.

Now, the all forums page is loading in around 1 second on average, which is 100% perfect for me.
BUT, once or twice its just died and gone to 30+ seconds. This time, "Page generated in 35.7500 seconds."
 
ok.
although the site works superbly now when its running correctly, it is still having little blips in performance.

Simple code is grinding it to a halt, and then coming back to full strength almost immediately

???? What is causing this? Is there someone else on the same server as me causing it?
 
It may be someone in EM, I am working to optimize MSSQL access via Enterprise Manager, currently on MSSQL3.
 
Just executed a 211 line query in Query Analyzer in sub 1 second times, it is running well now, and maybe the query I ran will speed things up.

Edit: Actually it should speed things up, but only testing will show for sure.
 
hi mate,
does this now apply to SQL1 also?

let me know and i will monitor.

As i said, the performance is now normally fantastic, but every so often it just dies and takes over a minute to load a basic page (And a refresh will load it in milliseconds!)
 
Yes, I went ahead and made the changes on MSSQL1 as well, it worked so well on MSSQL3, I figured I would go ahead.
 
OK, So EM is loading up properly for you?

Can you only see the temp, test, and your databases?
 
i just checked on MSSQL3 and yes i only see my databas and the default ones (temp , master etc)
 
Finally :)
The loading of 250+ database names was teh slooooooooow...

So slow that I just kept EM connected for several days in case I needed it :p

The way EM communicates isn't exactly designed for 'high' latency networks such as the internet, it only works comfortably on a LAN really :(
 
The latest service pack updates the way EM retrieves the DB list and makes a huge difference. It still takes a bit of time, but if I access MSSQL3 it only takes a coupel of minutes to show the db list.
 
largerrabbit,

Have you tested today, we should be talking seconds here now in time to show.
 
largerabbit said:
The latest service pack updates the way EM retrieves the DB list and makes a huge difference. It still takes a bit of time, but if I access MSSQL3 it only takes a coupel of minutes to show the db list.
Click to expand...
I installed SP3a some time ago, but I didn't really notice any difference at the time. I didn't time it, but it was still in the "too long" category :)

It's much better now.. I don't suppose it makes a significant difference for loading times, but why are the "pubs", "tempdb" and "Northwind" DBs on SQL1 set to Guest permission? They're not terribly interesting :)
 
Ooops!

Sorry Stephen you are absolutely right, it only takes a few seconds now to load the dbs, the big list is no more!!

What did you do to make this happen?

Thanks.
 
You must log in or register to reply here.
Back
Top