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
  • 9,052
  • 6
  • 46
  • 52
Rafael Simões
  • 31
  • 5

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,203
  • 6
  • 30
  • 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