Where are Temp Table and Table Variable Stored

         It is a confusing thing where temp tables and table variables are stored, Most of the people think that table variables are stored in memory and temp tables stored in tempdb. However both are saved and executed from tempdb. Lets check the below example.

CREATE TABLE #Temptable(Cust_Name VARCHAR(50))



FROM tempdb.sys.objects



            From the example its clear that both are created in temp db or disk, the first object is created for temp table and second one is for table variable. In terms of performance, I choose table variable, because its more efficient than temp table and also it is automatically dropped after execution.

Install and configure Master Data Services

Master Data Services

Master Data Management (MDM) defines a process of collecting enterprise data from various sources, applying standard rules and business processes, building a single view of the data, and finally distributing this ‘golden’ version of data to various systems in the enterprise, thereby making it accessible to all consumers.

Master Data Services is a Microsoft product for developing MDM solutions that is built on top of SQL Server database technology for back end processing. It provides service-oriented architecture endpoints using Windows Communication Foundation (WCF). We can implement a hub architecture using MDS to create centralized and synchronized data sources to reduce data redundancies across systems.

Install & configure Master Data Services

1. Double-click SQL Setup.exe, and follow the steps in the installation wizard.

2. Select Master Data Services on the Feature Selection page under Shared Features

3. Configure IIS on windows server.

4. Open Master data services configuration manager

5. Create MDS database by specifying SQL Instance and windows administrative account

6. Move to Web configuration tab and set up application pool for IIS

7. Open MDS web portal by entering IIS server name and application pool


Finding blocking/locking queries in SQL Server

     To resolve blocking issue in sql server we have to find out which process is causing the issue, and then if possible kill the blocking process. There are many different ways in SQL Server to identify a blocks and blocking process that are as follows.

Activity Monitor – Right click on SQL server and open tool Activity Monitor, You can use Activity Monitor to view information about the current processes. You can find out and kill blocked process using Blocked By drop down.

sp_who2 – It is the System SP help to find out running process in sql server. Once we execute procedure, we can find out blocked process from BlkBY column. But using sp_who2 we can find out which session is causing the issue but we couldn’t get the information about which Query part is causing the block or other information

Find SQL block using query – Using below query we can find out the SQL blocks, which process is causing the block, from that in which query part taking time to execute everything we can find out. I think this query part is very help full to identify or rectifying SQL blocks. 

       ,r.blocking_session_id AS 'blocked_by_SQL_Feathers'
QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' +
   QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS'stored_proc'
    ,CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time'
    ,CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time'
            '<?Running -->  ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                        CASE r.statement_end_offset
                            WHEN - 1
                                THEN Datalength(st.TEXT)
                            ELSE r.statement_end_offset
                            END - r.statement_start_offset
                        ) / 2
                    ) + 1) + CHAR(13) + CHAR(13) + '--?>'
            ) AS XML) AS 'query_text'
    --,qp.query_plan AS 'xml_plan'  -- uncomment (1) if you want to see plan
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp --  (2) if you want to see plan
    OR r.session_id != @@SPID
         r.cpu_time DESC