Very slow queueing with MS SQL 2008 R2

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

I have been using MS SQL 2008 (R2) for a while now. I recently set up my new development system on entirely new hardware and since I install Visual Studio anyways, I use MSSQL which comes with it.

I did the same on my previous system and it worked really well. With my new system, however, I see a significant (factor 4 or 5) slower queueing (and other DB) operations than before. Both systems run Win 7 x64 with latest updates. The new system has much better hardware (6-core 3930K CPU running at 4.2GHz, SSD hard drive and 32GB of RAM (16GB as ramdisk) and should (and does in other tasks!) perform significantly faster.
So, I am wondering if I missed anything.

I made sure MSSQL runs in simple mode (which it did out of the box; no change was required) and I am running the latest 3.0.7.2 beta of NMP.

Any ideas as to what might be going on?

administrator · Posted: Tue Apr 17, 2012 12:02 am Post subject:

Are you using the gradient highlight theme in NMP? Currently, that's causing a slow down when queuing new jobs. I think it is an issue caused by painting the icon over the gradient, but I'm still not 100% sure. I'm running the same version of SQL and if I turn off the gradient highlight, the queuing speed returns to normal.

Regards

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

I assume you mean the "List Highlight Style" in the general settings?
It was set to Glass and I have now changed it to Flat.
But I am in the middle of a big download and won't queue anything until the download is done in a few hours.
I just wanted to make sure I understood which setting you meant.

Did you see the slowdown also with Glass?

The menus and toolbars are set to the default theme btw.

administrator · Posted: Tue Apr 17, 2012 1:13 am Post subject:

Glass is fine. It's the gradient setting that causes the slowdown. In that case, it must be an actual issue with your SQL installation.

I would take a good look at how your install was done and make sure that you don't have any incorrect settings. Also, make sure that any firewall and AV settings are not interfering with SQL Server.

Regards

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

I ruled out firewall and AV by disabling both.
No change.

The install was done during installation of Visual Studio 2010, followed by windows updates, which upgraded to SQL server 2008 R2. I don't recall any install options.

You mentioned "incorrect settings". What could they be and how can I change them?

administrator · Posted: Tue Apr 17, 2012 11:05 am Post subject:

Most of the SQL Server performance articles on the Internet assume that the SQL Server itself is fine and that any performance problems are query or index related. However, the information used to diagnose a problem is the same in either case. The biggest hurdle to uncovering what might be causing your particular problem will be lack of load on SQL Server itself. Here are two very good articles to start with:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

This utility is useful in diagnosing SQL Server configuration issues:

http://sqlcop.lessthandot.com/

You should also review your SQL Server settings and verify that all of them are configured as you expect them to be. There are too many settings to cover here, but here is a start:

http://support.microsoft.com/kb/319942

If it were my machine, I would consider removing Visual Studio and SQL Server and reinstall SQL manually. Then install Visual Studio and let it use the instance you manually installed or a separate instance of its own.

Regards

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

Thanks a lot for the links.

I rather not re-install anything at the moment. I will leave that as a last resort in case the links don't lead to any help...

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

i just re-installed sql server 2008 r2 (full version, not express).

it is still extremely slow. no change. newsman is no fun any more!

i read the config articles you linked to but i found nothing that would be applicable to my situation.

any further ideas?

btw: it is not only the queueing that is extremely slow. also power purge which always ran very fast now takes ages. it seems the entire db access is messed up.

administrator · Posted: Sun Jul 22, 2012 2:30 am Post subject:

Here are a few more things to consider:

* When you reinstalled SQL Server, did you also create a new database?

* You may also want to try limiting parallel processing in SQL Server to see if maybe it is a compatibility issue with your CPU.

* Have you actually benchmarked your disks to compare iops between your old and new PC? SQL is, for the most part, disk bound.

* Have you limited SQL memory usage? If so, how much are you allowing and how big is your database?

* How many active databases are on your SQL instance?

* What other disk intensive activities are going on?

Trying to find these kinds of problems is frustrating, I know. I can say that I am using the same version of SQL Server and I am getting comparable performance as I was when using MySQL.

Regards

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

administrator · Posted: Sun Jul 22, 2012 11:18 am Post subject:

By all rights, it sounds to me like SQL Server should definitely run faster on your new PC than it did on your old one. I can't think of any reason why it wouldn't, especially since you are aren't even storing any long-term headers.

Have you tried MySQL on this PC? I'd be curious if it runs slower as well.

Regards

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

no, i haven't tried mysql on my dev system. but given that my current config is so frustratingly slow i am thinking about it.

the other option is to do a complete re-install and then follow your suggestion of installing mssql server first before i install visual studio.

i will probably make a decision in the next few days and i'll let you know how it goes.

in any case: thanks for your swift responses. your dedication to us customers is much appreciated!

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

i just installed mysql (latest 64bit version) and now newsman is very fast.

i will keep mysql for now.

my next software contract will involve a mssql database and i am curious how it will perform on my machine. i will then decide whether to re-install my entire system.

thanks again for your quick responses.

administrator · Posted: Thu Jul 26, 2012 2:00 pm Post subject:

While I'm glad to hear that MySQL is fast, it still doesn't explain what is wrong with your SQL Server installation. Doesn't make any sense to me.

Regards

haraldh · Experienced Joined: 12 Jun 2005 Posts: 124

yes, same here.
i would certainly like to find out what is going on.

if i gave you remote access to my machine (i have mssql management studio installed) would you be interested and willing to have a look?

if so, send me a pm and we can take it from there.