Sql-server – Which performance counters can you look at on a SQL Server Instance to determine it’s performance and over all health

Off the top of my head - top 3 query problems:

• Implicit conversions
• Bad indexing strategies (too many or not enough or the wrong kind)
• Using SELECT * instead of naming just the fields you need

There's a lot more around server-level config problems, database schema problems, hardware problems, etc. I wrote a script to quickly analyze servers looking for these kinds of problems:

http://www.brentozar.com/blitz/

Ever is a big word, but, in general, no, I wouldn't put a varchar(2000) field into an INCLUDE.

And yeah, the way that data is stored at the page level can seriously impact performance of the index, depending on how the index is used.

The thing is, the more rows of data you can cram into a page, the fewer pages have to get accessed, the faster your system is, for the most part. Adding a really large column means less information stored on a page, so, in the event of range seeks or scans, more pages have to be read to retreive the data, seriously slowing stuff down.

To know for sure if this is an issue on your query, or on your system, you'd have to monitor the reads, especially the number of pages that the query uses.