1

I have a DB Server running SQL Server 2008 R2 SP2 on 24 physical Cores and 32GB of RAM. The database files are on a SSD Raid1 and the TempDB on a SAS 15k (150GB DB)

I assume that the server has more than enough resources and the queries are running slow.

On my test environment the queries run quick on a low resources server, and in production they are getting stuck, lasting for 100.. 200 seconds for apparent no reason.

My indexes are not fragmented, my CPU activity do not pass 25%, my SSD activity are 5% max, are there any ideas to help me solve this problem?

marc_s
  • 8,932
  • 6
  • 45
  • 51
Rafael Simões
  • 31
  • 5
  • 1
    Can you run one of the slow queries in test and production and post the execution plans? – James Anderson Jan 20 '15 at 11:00
  • Is your SQL Server licensed correctly? Are you sure it's licensed for all its cores? If you're unsure, you can read up here – Reaces Jan 20 '15 at 12:40
  • To help understanding, i could exec an query now in 1 second and the exact same query 1minute later the same query takes 30seconds.

    I'm thinking in locks

     – Rafael Simões Jan 20 '15 at 14:13
  • Yes, check for blocking in sys.dm_exec_requests – Aaron Bertrand Jan 20 '15 at 15:07
  • @RR By running queries , you mean to say a manual run of query or you are trying this via Stored procs, coming from same or different hosts? – KASQLDBA Jan 20 '15 at 16:41
  • SP coming from the same host(app server) – Rafael Simões Jan 20 '15 at 17:35
  • How much data is in each system? If your test environment has 1% the records, then much longer run times in prod shouldn't be surprising. Also, are all, or most, or only a few queries much slower in prod? – Jon of All Trades Jan 20 '15 at 22:30
  • @rr is the max memory same between prod and test environment ? I ran into issues where prod was having more memory than test environment. Check my question . In my case TF2335 helped. – Kin Shah Jan 21 '15 at 12:20
  • The 2 systems as thw same number of records. The testing env. as only 4GB of ram – Rafael Simões Jan 21 '15 at 15:14
  • we are now suspecting of Lock Escalation to an entire Table caused by an Select... Yes an Select :) – Rafael Simões Jan 21 '15 at 15:17
  • @RR I believe to had a similar issue on one of my environments and update the stats as i listed below as my answer. Did you checked for table stats for the tables on a database in prod and dev server! Refer to below answer for further info – KASQLDBA Jan 21 '15 at 19:57
  • I believe i have discovered the problem. One TXT column as appeared with 67285Bytes and every time i select them with an where clause the SQL Server Engine lock the entire Table because the data volume on the select. – Rafael Simões Jan 22 '15 at 12:02

2 Answers2

0

  1. I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands.

  2. If these things don't help, check how the databases are set up. Is it possible that the data is identical, but server configurations are different, and, for example, there is a much lower threshold on available memory for the production version.

  3. As you said Indexes are not fragmented, Kindly check for the one with values above 30 % as FRAG value for pages greater than 1000.

You can proceed point 3 with Updating stats by running command: EXEC sp_updatestats; on database with slowness issue.

KASQLDBA
  • 7,159
  • 6
  • 27
  • 53
0

I believe i have discovered the problem. One TXT type column as appeared with 67285Bytes and every time i select them with an where clause the SQL Server Engine lock the entire Table because the data volume on the select.

Rafael Simões
  • 31
  • 5