Find out sql server hardware or speed test

Question

I use an sql server regularly and have recently been getting frustrated by the performance. It would be difficult for me to get direct access to find out the hardware so:

Is there a direct way in management studio to assess performance or find out the exact hardware.

Alternatively does someone have a set of test sql procedures I could try and ideally compare to other results to get an idea of it's performance.

So far I have setup a few quick queries on my local machines sql express server just as test these seem to run quicker than the sql server on the network which is meant to be high performance although no one knows when it was last upgraded I have a feeling it hasn't been for 6 or 7 years. Obviously these test don't account for the possibility of others querying at the same time or network transfers of results... Hopefully someone has a better solution.

Answer 1

You can't just ask your server guys? Seems like there's a fair bit of mistrust if you can't get hardware metrics. Count of CPUs, total memory, etc.

If there's that amount of mistrust, even if you found the answer from the database server, rectifying it would be impossible. If you can't get the current parameters, how could you get a change of hardware passed the server guys?

Start building rapport. The best line in the world to get someone on your side is, "I'm in trouble and I need your help..." You've elevated them and subjugated yourself, you've put them in a position to save you. You'd be amazed at how much you can get out of people that way.

As far as standard queries. You could look at TPC queries.

Answer 2

You also might try updating your references in your server. I had an issue a while back that 1 query returned in 100ms and an identical query in 5+ minutes and the only difference between the 2 was a Capital letter in the table name in my query (whih obviously shouldn't matter).

After some searching and SO-Questioning, I found that I needed to update my statistics. Could it be something like this is needed for your database / SQL Server too?

Answer 3

This sort of thing can be very political, especially in a firm with an endemic CYA culture (which describes most financial services companies). If there's no reasonable expectation of a good working relationship with the production staff, A few approaches are:

• Look at the query plans of the queries. Check that they are sensible (using indexes when they should etc.)

• Make it formal. Ask their manager to get the specifications of the machine, the disk layout and server configuration and the last time statistics were updated on all tables and indexes. Make it clear that the machine appears to be under-performing.

• If the statistics are out of date, get them updated.

Answer 4

IF you are on 2005:

SELECT * FROM sys.dm_os_performance_counters

That will give you some sql only stats. You will not find much info about the machine without at least terminal access. In the sql startup log you can see some info on processors as well.

Answer 5

and one more

SELECT * FROM sys.dm_os_sys_info