I set up an online dedicated windows server in Godaddy hosting. The server has 32GB RAM, 2TB Storage, and 4 CPU Cores @ 3.1 GHz.

I installed SQL Server 2017 Enterprise Edition. Problem is this the queries which are taking seconds in the local machine, now taking minutes in the dedicated online server.

In most cases I see this error in my ASP WebForms app as well: SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The performance issue sometimes exist and sometimes it works very good. If anyone guides me what do to fix this issue.

  • 1.How many user execute the same query. 2. local pc database may be lower version which restore the server – Mahfuz Morshed Nov 10 '18 at 17:08
  • in both environments, one user executes the query. I didn't get what you mean by second thing you mentioned. – Esmatullah Arifi Nov 10 '18 at 17:30
  • some instance not working in different version sql server – Mahfuz Morshed Nov 10 '18 at 17:40
  • 3
    The timeout is just because your queries takes longer than what you decided to wait, quite simply. So, you need to figure out why the queries are so slow on the new server. First step is to compare the execution plan between the fast (local) server and the slow (godaddy) server, for one of te queries where you have a significant difference. Ideally a query which don't return lots and lots of data (so you rule out the bandwidth aspect as much as possible). – Tibor Karaszi Nov 10 '18 at 18:31
  • 1
    My guess is that you got 2TB of super-slow storage. And/or that you are timing how long it takes to bring some massive resultset across the Internet and render in your local SSMS results grid. All the memory in the world won't help the latter. – Aaron Bertrand Nov 10 '18 at 21:12

You need to generate and analyze the WAIT statistics on your new server to proceed further on this issue. Also keep in mind that such hosting solutions share underlying disks among multiple customers customers. In that case although allocated space is dedicated but IO cycles are shared among multiple users of underlying storage. It deteriorates the IO while you see no obvious reason.

|improve this answer|||||

Since you are on SQL Server 2017 you have query store available. I would suggest enabling query store (if it is not already enabled) and compare timeframes of when you had degraded query performance vs when performance is good. Query store (in 2017) allows you to see both wait events at the query level, as well as execution plan regressions.

By seeing what waits are impacting your queries you will know whether to focus in on a hardware issue or something else (such as locking bad execution plans etc). Plan regressions will show if your issues are related to execution plan changes.

Analyzing waits or plan regressions in query store will take you down the right path to finding your issue.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.