question

Ramesh. To prove that SQL Server is performing well, you need to have system monitoring setup. If this is not running, you will still be able to take a look at your system and get a snapshot of current system activity using the DMVs. I suggest you read up on [WAIT STATS linked on Brent Ozar's blog][1]. This will help you to see if there are any current resource constraints on your system. It is advisable to setup some sort of system monitoring to gather some of these metrics so that you can show your devs/managers how the system is performing now and compare that to the normal baseline. [1]: http://www.brentozar.com/sql/sql-server-performance-tuning/wait-stats/

All you can prove at this point in time is how SQL Server is behaving. Since you don't have a set of performance metrics already collected you having nothing to compare against. The thing to do would be to immediately set up a good set of data collection using PerfMon, Trace, and some of the DMVs linked to in @WilliamD's post as well. With this information in hand, you can tell how the server is behaving now. If possible, get a collection of this information when you think the server is under the least amount of load possible. Then collect the data at the point when the server is under the most load. You can then compare the two. This will allow you to establish the concept of a baseline. Once you have a baseline, you can observe performance over time. In order to immediately answer the question, is the problem on the Web or your database, you need to measure a page request from the web, at the same time, measure the queries from that page request on the database server. Then, compare the two times. If the majority of the time is spent in your database, you're the problem. If the majority of the time is spent elsewhere, then that problem is somewhere else. There is no magic to any of this. You just have to measure and compare, tune, measure and compare, tune... etc.

As Grant said you should run the page request from the Web and run the sql query from the client, then compare the response. That best way you can do is, use SQL Server Profiler and trace how long your query take. I had same problem before with one of my developer who said that web page is slow because of my Database Server.However we had found out the problem with his web page.

While under high load, run Adam Machanic's sp_whoisactive and pinpoint offending processes and their current wait statuses. You go from there. If the DB is suffering, you might see waits on PAGEIO_LATCH, CXPACKET and LOGWRITER

When the system is slow: Do all the IIS servers slow? If yes then it might be the SQL Server or the network. It is likely to be something they all have in common. Do other systems using the same network slow? If so it is possible its the network Do other systems slow that share the SQL Server? If so it might be the server. If not then the server is likely to be OK. Can you access the database from SSMS with good response times? If not then it might be a database issue rather than a whole server problem. Do you know of any scheduled jobs happening at the same time - backups, file transfers, etc? They might be clashing for server or network resources. Do the slowdowns happen on a predictable basis? Other than this quick list I would point you towards the other answers here. they are all valid.

Do you have any information on how the website is developed and used? Are there a lot of Ajax and out of band calls hitting the db? What are the common tables being hit that may be causing the contention, user table/tables that populate drop down lists? What kind of caching are they using? One technique I use is create a job to export (small) highly active tables to xml. Load these into the cache at application start and create a cache dependency on these files so they are reloaded when updated. On high traffic sites this is a huge and you can eliminate millions of db hits