Sql-server – SQL Server 2012 Performance Test

I have had similar issues with SQL Server, it is possible that your server is not optimally configured. Newer Xeons come with TurboBoost, HT, etc. that can affect server performance significantly.

For example, we have had success with; Low Latency Configuration for Dell servers

The settings will be applicable to non-Dell servers, they just might have different names.

We also improved performance by setting the windows power management profile to high performance, from Balanced. A final piece is that it is recommended to reserve up to 8GB of memory for the OS on x64 servers, the default SQL install takes all memory. You might want to try 4/8GB reservation by setting your max SQL Server memory configuration to 4/8GB less than total memory.

My recommendation would be to revert to the old server if possible. If you do not have regression/automation/load scripts available, then the best you can do is to record your system activity for 1-4 hours during a high activity period. Then setup a web server the same as production, and a client machine to run the script. Run the same activity against the new server, make the configuration change and run the same activity again. Really you would want to do much more, but it doesn't appear that it would be viable and is outside the scope of this question.

With 2.5 million items I would expect your indexes to be rather large. Particularly if you are indexing on large fields. Also just as an FYI if your primary key is large that doesn't help either. Any non clustered index has the primary key (assuming that is your clustered index) in it regardless of if you include it or not. It has to as a reference back to the table.

So if you have a large primary key (and only if you have a large primary key) you might consider creating an identity column and changing that to your clustered primary key. You can keep your existing primary key as a non-clustered unique index. Also if you are worried about index fragmentation REBUILD/REORG it. It's part of the ALTER INDEX command in SQL 2008 and up. You can even do it on line.

My suggestion to you is to add the indexes you need (within reason). Generally 3 or 4 non clustered indexes isn't going to hurt performance. Honestly you can probably get away with 10 or so without a problem if you really need it. Test on your system as mileage may vary. Personally I would make sure that you have a clustered index as well. Also add a maintenance job of some type to do your index REBUILD/REORG. There are a number of them out there that will even check your current fragmentation and REBUILD or REORG intelligently as needed.