Abhijit More (4/1/2015)
Eirikur Eiriksson (4/1/2015)
Quick questions, what is the max memory settings for the sql server, what other services are running on the server (turn off anything not needed!)?, are you using RDP (kill all sessions!)? or are you logged on in a desktop session (log out!)?😎
max memory set to 24 GB. there are no other services, yes we are doing RDP.
em looking into Buffer Cache hit ratio, Page Life Expectancy, CheckpointPages/sec, Memory Grants Pending, Total server memory, Target server memory. but all sounds acceptable values.
I observed problem when I run 2 simultaneously or single heavy query on the server. sometime server response time is quick.
for example,
Single Query 1 running on 60 mil data took 13 min. But if I initiate any other query it takes 56 mins.
With multiple queries server responds badly.
Some quick observations. 24GB seems to be too high for a system with 30gb memory. I could be fine, but 22GB seems better for me.
That said, your data of 60 million records does not seem like that much. If multiple queries are running and it suddenly takes 56 minutes, have you bothered to look at execution plans, statistics, disk io, locking and blocking?
The query that is producing this performance, is it a query that was created by something like entity framework? Does the query hit nested views? Have you verified cpu use during these query runs (little things like the power plan can massively impact query performance and cause the type of thing you are seeing).
Above all else though, it really seems more like an opportunity to tune this code rather than trying to troubleshoot server settings.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events