Query hung to SQL Server on Azure

Question

Hi,

I have a large table (11 columns) with records around 30 million. when I am running a query

select count(ID) from

Answer 1

Hello Manish Singh,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that your Query in SQL Server on Azure is hanging beyond consideration amount of time, and you would like to optimize it.

To optimize your large table and improve query performance, you will have to do the rudiments and then troubleshoot slow queries either on a dedicated SQL pool or workload known issues.

Following rudiment strategies, you will work on the followings:

• Indexing https://www.sqlshack.com/query-optimization-techniques-in-sql-server-tips-and-tricks
• Partitioning https://learnsql.com/blog/practical-ways-to-improve-the-performance-of-sql-queries
• Statistics and Maintenance https://builtin.com/articles/optimize-sql-for-large-data-sets
• Limiting Data https://www.site24x7.com/learn/optimize-slow-sql-queries-for-large-dataset.html
• Execution Plan Analysis https://www.sqlops.com/best-practices-for-querying-large-datasets-in-sql-server/

To troubleshoot slow queries performance:

• https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries?source=recommendations
• https://learn.microsoft.com/en-us/troubleshoot/azure/synapse-analytics/dedicated-sql/query-execution-performance/troubleshoot-dsql-perf-slow-query?source=recommendations
• https://learn.microsoft.com/en-us/fabric/get-started/known-issues/known-issue-529-data-warehouse-more-than-20000-tables-fails-to-load?source=recommendations
• https://learn.microsoft.com/en-us/troubleshoot/azure/synapse-analytics/dedicated-sql/query-execution-performance/troubleshoot-dsql-perf-slow-query?source=recommendations
• https://learn.microsoft.com/en-us/dynamics365/human-resources/hr-developer-optimize-virtual-table-queries?source=recommendations

Check the above topics suitable for your server environment as it's listed in the additional resources by the right side of this page.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam

Answer 2

Which service tier do you have?

Blocking could be a possible reason. Make note of the spid of the window where you run the query (you see it in the status bar), then in a second window in the same database run

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <spid>

Is there any data in the column blocking_session_id? What is the wait type?