SQL Server 2016 SP1 Edition Limit Changes

Rich on 2017-02-02

width=724

Microsoft made the following changes in their documentation (see screenshot above) to accurately reflect the memory limits on lower editions of SQL Server.

Key Points

  • The limits for In-Memory OLTP data is per database.
  • The limits for Columnstore segment cache is per SQL Server instance across all the databases in the instance.

Example Scenario

A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by “max server memory” or total memory on the server if max server memory is uncapped.

MSSQL Find Last Date & Time Database Table Accessed

Rich on 2014-04-29

This is a T-SQL that will show all the last access date and time for Select, tables in a database.

select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()

Source: SQL Authority

Additional Information/Links:  SQL Blog

 

MSSQL Server Uptime Using T-SQL

Rich on 2014-04-29

A sample T-SQL for determining uptime of a Microsoft SQL Server.

USE master
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent < > running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

Example Output:

SQL Server "MSSQL01" is Online for the past 1438 hours & 7 minutes
SQL Server and SQL Server Agent both are running

Source: MSDN Social

Show Jobs that Will Run in Microsoft SQL Server

Rich on 2012-05-01

I don’t remember the link I found this little tidbit at, but it’s useful. This will display the scheduled jobs on your Microsoft SQL Server instance that will be running in the future.

USE msdb
;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND (SUBSTRING( CONVERT(VARCHAR(10),
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >
SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)
width=495

Screenshot

SQL Backup Jobs

Index Fragmentation Report in Microsoft SQL Server

Rich on 2012-05-01

I came across something that I found useful today when troubleshooting some query performance reports.

I won’t re-post the entire article, but here’s what I found useful and I tweaked it to my needs by ordering by avg_fragmentation_in_percent as well as only displaying results with IPS.page_counts greater than 1000 and avg_fragmentation_in_percent greater than 75%.

--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE foodb
GO
SELECT object_name(IPS.object_id) AS [TableName],
   SI.name AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   IPS.avg_page_space_used_in_percent,
   IPS.record_count,
   IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages,
   IPS.page_count
FROM sys.dm_db_index_physical_stats(db_id(N'foodb'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 and IPS.page_count > 1000 and avg_fragmentation_in_percent > 75
ORDER BY avg_fragmentation_in_percent desc
GO

Find Current Location of Data and Log Files for SQL Server

Rich on 2012-04-19

A quick method to find all the data and log file paths for all databases on a database server instance.

SELECT name, physical_name AS current_file_location FROM sys.master_files

Example Output:

name	current_file_location
master	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmaster.mdf
mastlog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmastlog.ldf
tempdev	E:MSSQLData	empDB.MDF
templog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA	emplog.LDF
tempdev2	E:MSSQLDATA	empdev2.ndf
modeldev	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmodel.mdf
modellog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmodellog.ldf
MSDBData	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMSDBData.mdf
MSDBLog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMSDBLog.ldf
ReportServer	E:MSSQLDataReportServer.mdf
ReportServer_log	E:MSSQLDataReportServer_log.LDF
ReportServerTempDB	E:MSSQLDataReportServerTempDB.mdf
ReportServerTempDB_log	E:MSSQLDataReportServerTempDB_log.LDF

Get ALL table and column descriptions in MSSQL

Rich on 2012-02-02

Get MS_Description from all tables AND columns in a database. Suh-weet.

SELECT          u.name + '.' + t.name AS ,
            td.value AS [table_desc],
                c.name AS [column],
                cd.value AS [column_desc]
FROM            sysobjects t
INNER JOIN  sysusers u
    ON          u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON          td.major_id = t.id
    AND         td.minor_id = 0
    AND         td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON          c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON          cd.major_id = c.id
    AND         cd.minor_id = c.colid
    AND         cd.name = 'MS_Description'
WHERE t.type = 'u'
--ORDER BY    t.name, c.colorder
ORDER BY , [column] ASC -- for my own use

Source: http://stackoverflow.com/a/887414