Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

My SQL Server doesn´t want to use the second CPU for our Vault database. I tried to use different configurations for the CPU affinity in SQL Management Studio, but doesn't seem to fix it. I've read an article about different values for affinity mask but I'm not sure which values are correct, the article was about 4 CPU´s and someone suggested to set the config value to 15. My Values for affinity mask are min: -2147483648; max: 2147483647; config_value = 0; run_value = 0; The server is virtual server.

Thanks in advance

share|improve this question
6  
Are you sure you are running queries that can take advantage of both CPUs? Why would you jump to the conclusion that you needed to change something with affinity mask, which is typically used to do the opposite? – Aaron Bertrand Apr 2 '12 at 10:27
What edition of SQL Server is installed? – mrdenny♦ Jul 22 '12 at 0:02

3 Answers

up vote 2 down vote

Check the SQL Log Viewer in SSMS from the last time the database service was restarted, it will note how many CPU's are detected; and like @Aaron Bertrand noted, be sure you are running queries that will take advantage of parallelism, and that your MAXDOP is set correctly.

share|improve this answer
I´m sure the Server could utilize both CPU´s while running data migration for our CAD database and it did so for one occasion, but then it changed back to only use 1 CPU and it surely could use the second since it´s capping out and access to the database get´s quite slow. @Aaron, I just started to see if the affinity mask would change something in the behaviour of the CPU utilization, it might have been the wrong idea. I checked the log file and it says: Detected 2 CPUs – loop29 Apr 2 '12 at 11:29
up vote 2 down vote

SQL Server uses a cost based system to figure out when a query should use multiple processors and when it shouldn't. By default any query with a cost of 5 or higher will be paralyzed by the SQL Server and that query will use multiple processors. Queries which have a cost of 5 or less will use only a single processor. Odds are that the queries are simply not expensive enough to require more processor resources. You'll want to leave the processor affinity at the default of 0.

Odds are that the bottleneck is your disks if you are running large queries not the CPUs.

When you are running queries what is the wait type being reported by the SQL Server? What edition of SQL Server is this?

share|improve this answer
up vote 1 down vote

Have you checked the option for "Max degree of parallelism"? You can do it by checking in Management Studio or by sp_configure:

EXEC sys.sp_configure N'max degree of parallelism'

This will answer if you are limited by setting. Otherwise SQL Server is smart enough to use whatever it needs. Rarely it uses less :-).

share|improve this answer
Thanks for the hints. The above query gives back the following information: min = 0 max = 1024 config_value = 0 run_value = 0 On general I have no idea if that Autodesk Data Management Server takes what it needs to run at max utilization. It´s a bit of an unlucky combination I have the feeling. So it´s just a front end running on top of the SQL server. I´m not doing any manual queries or programming, I just want to have it go at maximum speed.. this data migration just takes too long. I´m happy with any performance gains I can squeeze out of this virtual server. It runs 2 Opteron 6172 Processo – user7835 Apr 2 '12 at 13:07
So the result says that you're not limited by max CPU setting. This means the SQL Server will decide how much CPU to use on a query based on existing statistics, indexes and query hints (you can have MAXDOP setting per query, which can limit the no of CPU to use for a specific query). – Marian Apr 2 '12 at 13:51
where do I find that setting MAXDOP? Is it under General Configuration for the database instance? The Migration task I´m doing right now is already running for 2 hours and it utilizes only 1 processor on the server, which makes me wonder if it´s doing its job. The database comes from an SQL Server 2005 Express installation, which had the limitation of that version. I still fear that it somehow has passed on that limitation. Thanks – loop29 Apr 2 '12 at 14:03
No, the general setting can be found using right click on the server -> Server Properties -> Advanced -> section Parallelism -> Max degree. The setting per query is..well..under each query :-). You need to open an SQL Profiler and follow all the application queries on your specific db and server and see if any of the long queries have OPTION (MAXDOP) as a hint. – Marian Apr 2 '12 at 14:08
Ok, thanks for the advice, I´m currently running the profiler and see what it turns up. Remember that this is a Autodesk Data Management Server on top, I have no idea what kind of transactions it is doing. – loop29 Apr 2 '12 at 14:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.