Sql-server – Inserting Values into Temp Table

sql servert-sql

Can anyone help me to work out below T-SQL code. I am facing issue while adding the second value. The first value gives the state of SQL Service and second value gives the SQL last restart time. I am not a core developer so can anyone help me .

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1 
([Current Service State] nvarchar(200),
[SQL_Server_last_RestartOn]nvarchar(200))

insert into  #temp1
EXEC master.sys.xp_servicecontrol N'querystate',N'MSSQLSERVER',
(select min(login_time) from master..sysprocesses) as SQL_Server_last_RestartOn

Best Answer

Give this a try

IF object_id('tempdb..#temp1') IS NOT NULL
    DROP TABLE #temp1

CREATE TABLE #temp1 (
    [Current Service State] NVARCHAR(200)
    ,[SQL_Server_last_RestartOn] NVARCHAR(200)
    )

INSERT INTO #temp1 ([Current Service State])
EXEC master.sys.xp_servicecontrol N'querystate'
    ,N'MSSQLSERVER'

UPDATE #temp1
SET SQL_Server_last_RestartOn = (
        SELECT min(login_time)
        FROM master..sysprocesses
        )

SELECT *
FROM #temp1

Questions / Further information required

Please confirm processor name and type (I'm basically trying to establish if it's 2 x hex-core with HT ). Use System information (eg Control Panel > System and Security > System on Windows Server 2012 R2) and / or sysinternals tool CoreInfo to confirm.
Please confirm server maxdop ( eg EXEC sp_configure 'max degree of parallelism' ). If the CPUs are hex-core, the server maxdop should be at most 6 (as per here), or arguably lower on an OLTP system. I normally keep my tempdb files in line with my server DOP to a max of 8 but we'll come onto that.
Please confirm server total memory on the box and the SQL Server memory cap ( eg EXEC sp_configure 'max server memory (MB)' ).
Please confirm if any other services are running on the box ( eg SSIS, SSAS, SSRS, the application, iTunes etc )
Please confirm Instant File Initialisation is enabled for the SQL Server service account. (Ways to test it here).
Why is there such a massive discrepancy between the CPU (beefy 2 node NUMA setup) verus the one disk (home PC)? Consider adding disks, striping, SSD for tempdb (although avoid over-reacting : ).
Please add an actual execution plan for one of the problem queries. Anonymise with SQL Sentry Plan Explorer if you wish.
Hash joins with table variables in an OLTP system? This suggests a lack of indexing on the table variable, main table or both. Are you declaring your table variables like this (with no indexes)?
```
DECLARE @t TABLE ( x INT )
```

Don't skimp on the table variable definition even though it's holding small resultsets. It's always best to give the optimizer as much information as possible so be explicit with nullability, uniqueness, whether or not the index is clustered / non-clustered, eg

DECLARE @t TABLE ( x INT PRIMARY KEY )
DECLARE @u TABLE ( x INT PRIMARY KEY NONCLUSTERED, u INT NOT NULL UNIQUE CLUSTERED, z INT NOT NULL UNIQUE, a CHAR(1) NULL ) -- not sure why you would do this but you can
DECLARE @v TABLE ( x INT NOT NULL, y INT NOT NULL, PRIMARY KEY ( x, y ) )   -- multi-column primary key

Posting the execution plan will help diagnose this.
Check for code preventing table variable caching as per here, here. I think dynamic SQL and proc executed WITH RECOMPILE are the only ones that affect table variables.
```
DECLARE @u TABLE ( x INT )

INSERT @u
EXEC('DECLARE @t TABLE ( x INT ); INSERT INTO @t VALUES ( 1 ); SELECT x FROM @t;' )

SELECT *
FROM @u
```
Check SQL Server Log ( Object Explorer > Management > SQL Server Logs ) for messages, eg IO warnings.
Check Windows Event Viewer
There have been a number of builds released since SP1. Review the CU fixes put in since SP1. It's possible there are bugs in SP1 fixed in subsequent CUs, eg FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct https://support.microsoft.com/en-us/kb/3088480
Establish this is your cause before applying any hotfixes, although it is more important to keep up-to-date with CUs with SQL Server 2014, due the number of new features (in-memory OLTP, clustered columnstore).
Finally, the need for one tempdb file per core is a myth and looking at your disk setup my guess is tempdb is overly fragmented. I have a nagging feeling you have one disk-head, tempdb has one filegroup, many files.

However forget what we think we know; create a test rig which reproduces your problem, and experiment with reducing the number of temp files ... start at 1, 2, 4, 6 etc gather the information, to make an evidence-based decision. Now this is the harder bit as your problem seems intermittent and you may not be able to mess with your tempdb setup, but that's how I would approach this.

Good luck. Let us know how you get on.

Best Answer

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

Sql-server – TempDB contentions

Questions / Further information required

Related Question