Unanswered: SQL Server Performance - Hardware Or Application Problem
Hi everyone I am analyzing the performance on my SQL Server and need some advice on why I am seeing the results that I am.
One of my problems is that the SQL Server takes an extremely long time to execute the following command 'exec sp_spaceused' on my tempdb. This command usually displays how much free space is remaining on the selected server.
My other problem is that one of the applications that use SQL Server as a back end is acting extremely slugish. Navigating through screens, performing functions and refreshing grids take some time to execute however when I do a trace in SQL Profiler the queries seem to be executing instantly. My first thought was that the queries executed by the application needed to be optimized, but when I do a trace on the queries spawned by the application they seem fine. Does this mean that I have a network bottleneck problem or possibly a PC problem? The PC that we use to do our testing is a P4 3.0 with 512MB of ram running on Windows 2000. The SQL Server is running on Windows 2003 Server with a Raid 5 configuration 4 zeon 3.0 processors with 4 GB of ram. I feel that our desktop PC are sufficient to run any application but have received suggestions that I should upgrade my client PC's to at least 1GB of ram or move to a Citrix server, configure my SQL Server for Raid 10, try file splitting, use gigabit connections from the SQL Server to the desktops etc.
Given the facts above can someone give me advise on the most logical way to resolve my performance issue. Is my problem on the application end of things or do I need to change my network architecture and or hardware?
1) I assume the CPU load is low, is this correct?
2) What is the average disk queue length on the disk array(s) containing tempdb and the database?
3) What is the peak and average disk reads/sec on thos drives? And disk writes?
4) How many disks are there in the RAID 5 set, what speed are they (5400/7200/10k/15k)
5) Obvious, but still: Are there heavy use of cursors or temporary tables?
6) Equally obvious, and still: Does the sql server or the application the filtering when the application selects data? IE: Does the queries use where clauses to filter data efficiently?
These are among the questions that (alone or combined) may give us a clue about what's wrong.
My other problem is that one of the applications that use SQL Server as a back end is acting extremely slugish. Navigating through screens, performing functions and refreshing grids take some time to execute however when I do a trace in SQL Profiler the queries seem to be executing instantly.
How many rows are typically returned? Any BLOBS or smilar? any of the queries doing something like SELECT * and the app only using a field or two? Is there an appreciable difference if you run these queries using QA\ SSMS locally on the server and remotely from a client? What is the application program? (for example, if it is something like Access and you use Access SQL syntax in the where clause or a VB function the filtering is done at the client not the server).