3

I am recently facing a troubling issue with SQL Server. I am using a node program to connect and run queries. As number of request/db queries increase, it seems SQL Server performance is degrading.

I used a basic test scenario, where I get around 6 concurrent requests, which in turn triggers 6 database queries in parallel. As result evaluation time for each query got roughly multiplied by 6 times.

So, basically when I hit a single query, it takes around 120ms, but in case 6 similar queries are in parallel, each query takes around 7500ms which is a troubling issue with concurrent requests.

For the record, I am using SELECT queries which should be non-blocking.

Any help or information would be deeply appreciated.

Updates

I checked the table and it doesn't have any indexes. The query that I have tested is a simple SELECT query with a WHERE clause. I have also checked sys.dm_tran_lock for database lock information and found that it is creating IS lock on that table for each request query. It is SQL Server 2012 running on Azure instance. I am not sure about the hardware specs but it should be up to standard.

I have played around a little trying combination of queries, seems like it hits most when the concurrent queries use a same table/view.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gautam Kumar Samal
  • 758
  • 1
  • 7
  • 23
  • 2
    I think you're making a lot of assumptions here. Have you actually checked locks? What indexes do you have on the table(s)? What queries are being run? What is the server hardware? There's a lot to consider that we can't know from your post. Maybe check out the free sprocs from [Brent Ozar](https://www.brentozar.com/responder/) and review the results? – Jacob H Jun 14 '17 at 20:44
  • @JacobH: Edited the question, Please review. – Gautam Kumar Samal Jun 14 '17 at 21:38

0 Answers0