Query performance 16GB RAM and 256 GB RAM -- no difference

Transact-SQL
1

Hello Team,

Any help would be highly appreciated. I have SQl 2012 with 16GB RAM and a 10 MILLION record table. I am merging everyday the whole 10 million records. I know its bad practice..but this is DEV and i am purposefully doing this. On SQL 2012 with 16GB RAM(Memory allocated for SQL Sever is 12GB), its taking 10 min and the same time with the same query on SQL 2014 with 256GB RAM. the memory for SQL Server is 160GB(for SQL 2014). Are there anyadmin level changes that i need to do for better performance? I have restarted SQL Server more than 2-3 times.

2

That is probably and indication that memory is not the bottleneck. My guess would be that the reading and writing of the records is the main culprit You could look at the wait stats to get an indication as to whether that is the case, or whether it is something else. http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

3

Thanks. i see this and appears that the issue is with parallelism. i even have 32 core system now and previously only 8. MAXDOP option is set to DEFAULT which i believe is OK.

4

No, you want to limit MAXDOP to less than the default. You should also lower the "threshold for parallelism".

Naturally table indexes and the code itself might need tuned as well.

5

So same query running on 16GB, 8 Core will not make a difference with 256GB and 32 core?

6

only if CPU is the bottleneck

7

CPU bottleneck makes sense. So my option is just to change the MAXDOP option and test it out?

8

No, it's not a CPU bottleneck. It's most likely an I/O issue, since extra RAM didn't help.

You need to verify that there is no underlying I/O issue, such as a bad controller, then adjust table indexes and/or how the query is coded to address it.

Obviously it's impossible to tell you exactly what's wrong with a query we don't even get to see!

9

agree with you related the query. Its a simple query which joins on on non-clustered index and unique_clustered_index. Thanks everyone for all your ideas though.

10

So you've looked at the query plan and verified that it joins on the non-clus index? Does it do a SEEK on the non-clus index or a full scan of the index?

11

Yeah, its a Clustered Index Seek :slight_smile:

And just an FYI...on a daily load, there will not be more than 100K records and the query finishes in less than 5 sec. But with 10 million records(i want to see the difference between old server and new server), there is no improvement with big load.

12

OK, I give up. I'll also assume your query code is perfect. In which case, no, there are not any admin level changes that should give you noticeably better performance (other than perhaps another task blocking yours from running). I hope you find something.

13

I will try to change some of the MAXDOP options and see if it helps.