July 12, 2011 at 7:15 am
Greetings all, first post.
Got an issue that I don't really know how to deal with.
We're bringing a new application online in a few months, and management is a bit spooked about how it will perform.
So in order to gauge DB performance my manager (a non-db person) has setup a tool to run a query to test the response time of the DB server.
For the most part, this tool returns sub-second response, with the exception being about once an hour, a three second response is recorded.
In the mean time, I'm running a Powershell script that executes the same query on a machine located in the same rack as the DB server, and am achieving consistent sub-second response. Out of 5860 samples, 5681 are completing in under .100 milliseconds; 5820 are completing in under .5 seconds. Of the remaining 40 samples, only 3 are above .7 seconds.
FWIW, the query is returning about 3300, 50 byte rows.
My manager thinks that the spike that he is seeing, coupled with the above .5 second responses from my query are an indication of a problem with the database server.
My position is that at the sub-second level, there are going to be variations in response time that can be attributed to any number of factors. I'm attributing the spike his tool is recording to an anomaly in the tool, since I'm seeing nothing like it.
At the moment, the DB server is fairly idle, with only the test team (about 15 people) doing any work.
I'm also recording statistics that are establishing our baseline for when the real workload hits. All the stats are well within acceptable values.
He is making a big deal about this, and I would like to know what is considered an acceptable tolerance in the variation of query response in order to get him on board with what is really happening.
Thanks in advance for any insight on dealing with this.
Don
July 12, 2011 at 7:28 am
on the dbserver, are there any other services going on in it is it doing Exchange as well as SQL, or web hosting?
does the server also host, say, the company's SharePoint services as well?
there are other things to consider for performance than just the query response time.
what kind of network backbone is behind the whole framework? big queries on a slow network might seem slow.
if you look in the servers logs at teh exact moment that slightly slower query occurred, could it have been doing something else?
I'm looking forward to this thread; could be very educational.
Lowell
July 12, 2011 at 7:34 am
Seems to me like it is pretty hard to guess the performance of a new system by running sample queries that may or may not be relevant to the new system. Seems to me that you have done a solid amount of sanity testing and everything except a couple of outliers are performing quite well. Full system testing is where you can identify any performance issues. There is really no kind of testing tool that will generate the same kind of real world proof like actual system testing. I agree with Lowell that this thread could prove to be very educational.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply