Queries execute slow what is the issue?

This is a very loaded question, there is no simple answer.  But few things to make sure before you go crazy on PTO, make sure your indexes are maintained for the databases.  If we execute query similar to [6], just with “index_id > 0”; that is I only want to know about clustere and non-cluster indexes. Please note I am supplying in db_id() function, therefore it must be executed in context of the database that is giving you issues.

  SELECT   OBJECT_NAME(IPS.OBJECT_ID)       AS TableName
         , CASE WHEN i.name IS NULL THEN
              'HEAP TABLE, NO INDEXES'
           ELSE
              i.name
           END                              AS IndexName
         , index_type_desc                  AS IndexType
         , index_depth
         , avg_fragmentation_in_percent
         , page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') IPS
    JOIN sys.indexes I ON IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
   WHERE I.index_id > 0
ORDER BY avg_fragmentation_in_percent

Information returned by this query can daunting on larger databases; but general idea is, to be cautious of is the average fragmentation.  It should be low as possible, but if it is higher then 10% you might have to look at defragging your indexes.  If this query returns no rows, you got serious configuration issue on database because it means the there are no indexes on the user tables.  If it only returns rows where the index_id is 2 then it means the database does not have any cluster indexes which can be host of other issues.

Another DMV, SQL Statement you can use to see whats happening on your SQL Server is the dm_io_virtual_file_stats; you can see relatively which file on your server is being accessed the most.

-- Support Statements
DECLARE @DBID AS int

-- Step 1: Get Database ID for which you wish to get information for
SELECT @DBID = db_id('master')

-- Step 2: Stall Read Information for the Database
  SELECT DB_NAME(database_id) AS DBName,
         file_id,
         CASE WHEN num_of_reads = 0 THEN
            io_stall_read_ms / 1
         ELSE
            io_stall_read_ms / num_of_reads
         END AS AVG_IO_StallTime_Reads_ms,
         CASE WHEN num_of_writes = 0 THEN
            io_stall_write_ms / 1
         ELSE
            io_stall_write_ms / num_of_writes
        END AS AVG_IO_StallTime_Writes_ms
   FROM sys.dm_io_virtual_file_stats(@DBID,null)

UNION

  SELECT DB_NAME(database_id) AS DBName,
         file_id,
         CASE WHEN num_of_reads = 0 THEN
            io_stall_read_ms / 1
         ELSE
            io_stall_read_ms / num_of_reads
         END AS AVG_IO_StallTime_Reads_ms,
         CASE WHEN num_of_writes = 0 THEN
            io_stall_write_ms / 1
         ELSE
            io_stall_write_ms / num_of_writes
        END AS AVG_IO_StallTime_Writes_ms
   FROM sys.dm_io_virtual_file_stats(2,null)
GO

After all that if the issue are still not obvious you can do to some SQL Profile Logging with following events; then we can run this through some SQL Utilities (like ClearTrace) that will tell us where are the performance impacts are with in SQL Server.

Events at minimum you should log:
Stored Procedures/RPC: Completed (SPID, CPU, Reads, Writes, Durations, TextData, StartTime)
TSQL/SQL: BatchCompleted (SPID, CPU, Reads, Writes, Durations, TextData, StartTime)

After logging this data, running ClearTrace against it gives you overview of where the issue might be.

Last but not least to find out if there are blocking issues, please refer to [2] on how to enable blocking check in SQL Server.  And then create another SQL Server Profiler trace with following events to see what blocking is occurring (feel free to email me, I can try helping with the report).

Events to log for blocking:
Errors and Warmings/Blocked Process Report (All Columns)

Performance tuning SQL Server is lots of work and there are complete books dedicated to it.  But this gives some of the basics on where to start to investigate the issues.

Reference Links

1. Clear Trace. Link.
2. Enabling Block Report Check in SQL Server. SQLLearnings. Link.
3. Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning) Link.
4. The Guru’s Guide to SQL Server Architecture and Internals Link.
5. Microsoft SQL Server 2008 Internals (Pro – Developer) Link.
6. SQLCAN. Mohit Gupta. Getting List of Fragmented Indexes in a Database. Link.