Our company has twenty users on a domain environment. We are running SQL Server 2014 on Hyper-V with static RAM of 136 GB and 32 virtual processors. The VHD is also static.
The host machine has a total of 192 GB RAM and two, 16-Core processors (Intel Xeon).
We have only the one SQL server with about 12 databases. We are in the painful mode of migrating our query logic from pass-through queries in Access to stored procedures in SQL. Years of VB code are being rewritten, so this is going to take several more months.
In the meantime, we are incredibly frustrated with performance. The most common issue is ODBC timeouts using Access queries. The funny thing is that the same query may run just fine on one workstation, but not another. Then, the next minute it is fine. Other times, there may not be a timeout error but it's just super slow.
We invested in a 10Gb backbone and have teamed up the NICs on the server to ensure bandwidth is not the issue. There is plenty of hard drive space. The SQL server and data resides on the server's RAID-5 array. We have regular maintenance for indexing, etc.
What other areas might I check? Is it possible that 15-20 users is too much for this kind of SQL configuration? I am just trying to see what threshold we are up against. Thank you for any guidance.
I have looked at the performance monitor within the profiler, but I have not been able to get much information. Most likely due to my own ignorance of SQL. Anything in particular I can focus on? Thank you.
I wonder if it has something to do with your proc not benefiting from caching execution plans.
Also are you doing any sort of dynamic queries using exec(@sql)
Here is a query you can run on your database with the slowest stored procedure to see if is it being cached. UseCounts should tell you if it
select UseCounts, cacheobjtype, objtype, TEXT, query_plan, cp.bucketid
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(plan_handle) sqlT
cross apply sys.dm_exec_query_plan(plan_handle) qp
where 1 = 1
and TEXT not like '%dm_exec_sql_text%'
and TEXT not like '%sys.%'
and objtype = 'Proc'
Are those Access queries done as pass-through queries to SQL Server - or are you using linked tables? If using linked tables you need to make sure all tables have appropriate indexes - especially primary keys, and that the linked tables have identified those keys.
The next thing to do is review those Access queries - and if they are moderately complex they can and will cause timeouts as Access is probably trying to pull all of the data across into Access before applying the join and where conditions.
You can replace those Access queries with either stored procedures or views - depending on how those queries are set up...if they have parameters they need to be stored procedures or you can move the core of the query to a view - and then use the view with your parameters in an Access query.