April 20, 2016 at 2:56 am
Hello, we are now running a Microsoft SQL Server 2012 instance in a 2 processors machine, but now we will implement a service with 700K transactions per day (insert,updates or deletes). Now our CPU is stressed, so we know that we have to upgrade the machine to get a good performance, but we would like to know if it is possible to manage/limit processors used per database, because this new transactions will be on a database and we don't want to get worse perfomance on the others databases.
We have been searching for documentation and we have found Resource Governor, but we would like to know any experience or any other solution.
Thank you very much.
April 21, 2016 at 8:53 am
April 21, 2016 at 9:18 am
Is moving the database to it's own server an option?
May 18, 2016 at 8:28 am
Indexes are not an option, because these 700K transaction are about 600K inserts and 100K deletes.
It is on our own server.
May 18, 2016 at 8:39 am
celia.moreno (5/18/2016)
Indexes are not an option, because these 700K transaction are about 600K inserts and 100K deletes.It is on our own server.
you should still review the indexes.
a classic example is if the table is using a GUID for the clustered index/primary key;
inserted rows will cause a lot of page splits and IO as new records are inserted.
the more details about what is going on, the more we can help.
Lowell
May 18, 2016 at 9:00 am
The main question is if it is possible to manage/limit processors used per database.
We have to add some logic to this data and this will penalize processor disponibility to other applications that use other database from same server.
We are question us if it is better:
- to have other server with this database
- to have other SQL instance and limit cores usage for each instance
- to limit cores usage for each database
For last two options we don't know if it is possible to have for example a machine with 8 cpus, and limit 1 database to use first 4 cpus and other database to other 4 databases.
May 18, 2016 at 9:27 am
May 18, 2016 at 9:49 am
celia.moreno (5/18/2016)
The main question is if it is possible to manage/limit processors used per database.We have to add some logic to this data and this will penalize processor disponibility to other applications that use other database from same server.
We are question us if it is better:
- to have other server with this database
- to have other SQL instance and limit cores usage for each instance
- to limit cores usage for each database
For last two options we don't know if it is possible to have for example a machine with 8 cpus, and limit 1 database to use first 4 cpus and other database to other 4 databases.
That may be your main question, but it may not be the first thing you should be looking at. I would look at the indexes on the tables in the database affected by the 700K transactions/day. I would also look at the queries used for the inserts/updates/deletes to see if they can be improved as well. Don't sell these ideas short. They are worth looking at, especially before looking at throwing more hardware at the problem.
May 19, 2016 at 1:19 am
Thank you very much, I will check indexes when we'll implement this feature. Now we have CPU stressed because we have a lot of processes running, so when we'll implement this new one, we don't want to affect to the others, this is why we are planning a hardware upgrade and the way to separate processes.
May 19, 2016 at 2:46 pm
Resource Governor is Enterprise Only - https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.110%29.aspx
If you are running enterprise, I'd buy more CPU/RAM to take advantage of capabilities. Certainly you can use RG to limit CPU by user, who is mapped to a group. If you have users in multiple databases, then you can't necessarily limit the CPU usage by database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply