HeapTable

Heap Table:   A table which doesn't have a clustered index is called Heap table.

We can describe a 'heap table' let SQL server do a table scan on it while retrieving data, which obviously not good. So try to avoid heap tables in your database to give 100% chances to get the performance.

You can find the heap tables by using following T-SQL statement.

USE [YourDataBaseName]
GO

SELECT SCH.name + '.' + TBL.name AS TableName
    FROM sys.tables AS TBL
    INNER JOIN sys.schemas AS SCH
         ON TBL.schema_id = SCH.schema_id
    INNER JOIN sys.indexes AS IDX
         ON TBL.object_id = IDX.object_id
            AND IDX.type = 0 -- = Heap
    ORDER BY TableName   

Creating "Dynamic TABLE" by using T-SQL Script

Some times there will be a situation, Your manager/front end colleges feel how good our code generate a dynamic table to load the appropriate
data on the fly.
I want my script to generate like MyTestTable_TodayDate with few columns as requested...
It looks as below
        CREATE TABLE MyTestTable_20140403
                        (
                             Sno INT
                            ,InsertDate DATETIME
                            ,EntryBy VARCHAR(30)
                        )
                      
-- You can use the following script to do that.
Script :
        -- Declare Dynamic Value, In this scenario it is date
        DECLARE @DynamicValue VARCHAR(10)
        -- Just formatting how I would like to see the dynamic value
        SET @DynamicValue = Replace(CONVERT(VARCHAR(10),GETDATE(),120),'-','')
        -- Check what date format will be adding...
   -- Note Test only:     SELECT @DynamicValue

        DECLARE @str VARCHAR(1000)  -- Make sure the length of the string you are passing

        SET @str = 'CREATE TABLE MyTestTable_'+@DynamicValue+'
                    (     Sno INT
                        ,InsertDate DATETIME
                        ,EntryBy VARCHAR(30)
                    )'
    -- Note Test only: It tells the final creating table syntax
        PRINT (@str)
        -- Which is the execution command, Creates the dynamic Table
        EXEC (@str)
      
       

when did last time a stored procedure executed?

SELECT    Database_id,
        OBJECT_NAME(object_id, Database_id) AS 'ProcedureName',
        Cached_Time,
        Last_Execution_Time,
        Total_Elapsed_Time,
        Total_Elapsed_Time/Execution_Count AS Avg_Elapsed_Time,
        Last_Elapsed_Time, Execution_Count
FROM sys.dm_exec_procedure_stats
WHERE --DB_Name(database_Id)= 'YourDB'  and -- your database ID
OBJECT_NAME(object_id, Database_id) = 'Your ProcedureName'
ORDER BY [Total_Worker_Time] DESC;

Find SQL Traces, currently running

SELECT * FROM sys.traces -- Will show all the traces

You will get so many details by running the above statement regarding the current traces.
By default one trace is running.
You can see the location/Path where the trace file being sitting, Start time, Stop time, How much max. size the trace file holds up to, in how many files it Can hold the data end so on.

Note: You can give your own size and own number of files while starting the Trace.

SELECT * FROM fn_trace_getinfo(0) -- Show all traces

It will give you minimum but almost all important fields information as result. But you have to process the trace number as Input in the function.

SELECT * FROM fn_trace_getinfo(NULL)
will to same as above.

If you decided to Stop/Delete a trace which is currently running, you can use below commands.(Note: You have to provide the trace id)

sp_trace_setstatus  @traceid =  2,  @status =  0    -- It will stop the trace by providing Traceid..

 Even though the trace is stopped, it still showing up on the traces list. You can completely delete that by using following command.

sp_trace_setstatus  @traceid =  2,  @status =  2   -- It will delete the trace from the list by providing Traceid..

Quick check : What is running on my server at the moment

You can quickly run SP_Who2 to see what is running on your server at the moment. Of course it will give you  current transactions and pending (currently processing) transactions also called as open transactions.

If you use SP_Who2 Active, you will just get Current transactions.

Apart from these commands you can get interesting information with dmv's, introduced in sql 2005 and also applicable for later versions of 2005.

DECLARE @TimeStamp DATETIME
SET @TimeStamp = DATEADD(MINUTE,-10,getdate())  -- You can pass your own string here

SELECT login_time
        ,last_request_start_time
        ,last_request_end_time
        ,DATEDIFF(millisecond,last_request_start_time,last_request_end_time) AS 'Milli'  -- Dynamic column
        ,host_name
        ,PROGRAM_NAME
        ,host_process_id
        ,client_interface_name
        ,login_name
        ,nt_user_name
        ,total_scheduled_time
        ,total_elapsed_time
        ,reads
        ,writes
        ,logical_reads
        ,original_login_name
    FROM sys.dm_exec_sessions WHERE
    last_request_end_time > @timeStamp
    ORDER BY login_time

SQL Server Versions History

The glance you can find the version history of the sql server as released...

Version	Year	Release Name	Codename
1.0 (OS/2)	1989	SQL Server 1.0 (16 bit)	-
1.1 (OS/2)	1991	SQL Server 1.1 (16 bit)	-
4.21 (WinNT)	1993	SQL Server 4.21	SQLNT
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0 OLAP Tools	Palato mania
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64-bit Edition	Liberty
9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai
10.25	2010	SQL Azure DB	CloudDatabase
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11.0	2012	SQL Server 2012	Denali
12.0	2014	SQL Server 2014	Not Named Yet.....

Do you know this? (Smallest VLF in size) / (Minimum Size of Transactional Log)

VLF: Virtual Log File.

The smallest size of VLF is 256 KB.

The minimum size of the transactional log 512KB.

 Please check at My Previous VLF related Post to know little more about VLF's.