How to get all statistics information for a database in SQL Server

470 views 19:10 0 Comments 4 October 2019

Introduction:

Sometimes your query gives you very bad performance because of statistics as SQL database engine Query optimizer uses statistics to create query plans. Updated statistics will give you good performance.

To get the statistics information for a particular database, you can use the below script. You can update the statistics by using the below stored procedure.

exec sp_updatestats

Script:

-- ============================ --
-- Owner: SOYELUDDIN BISWAS     --
-- Created Date: 03/10/2019     --
-- Email: st.biswas99@gmail.com --
-- ============================ --
--select OBJECT_ID('#AllStatistics')
--Please run this query to specifc database to get all the statistics informations
DECLARE @TableName NVARCHAR(100);
DECLARE @StatName NVARCHAR(100);
DECLARE @UpdateDate DateTime;
DECLARE @NoOfRows BigInt

IF OBJECT_ID('tempdb..#AllStatistics') IS NOT NULL DROP TABLE #AllStatistics
CREATE TABLE #AllStatistics
(
TableName NVARCHAR(100),
StatName NVARCHAR(100),
UpdateDate DateTime,
NoOfRows BigInt
)
INSERT INTO #AllStatistics exec sp_MSforeachtable 
'SELECT OBJECT_NAME(stat.object_id),  
stat.name, 
last_updated,
rows
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id(''?'') and stat.name not like ''_WA_%'''

SELECT 
TableName AS [Table Name],
StatName as [Statistics Name],
UpdateDate as[Last Update Time],
NoOfRows as [No of rows] 
from #AllStatistics Order by 1 ASC

DROP TABLE #AllStatistics
-- rows= Total number of rows in the table or indexed view when statistics were last updated. 

Result:

SQL Database statistics
SQL Database statistics

One thought on “How to get all statistics information for a database in SQL Server”

  • You have a very nice website, but I’m sure it’s pretty hard to find new article ideas and create content especially nowadays when it seems like all topics have been covered by someone..

    I’m sure you’re tired of getting these messages through your contact form, and that’s why I won’t bother you with long texts. I just think that you’ll have a lot to gain from this small tip:)

    I have been able to create articles easily for all my websites and avoid any duplicate content penalties from Google using a very smart and intuitive article spinner called Spin Rewriter, which I`m sure would work wonders on a business like yours. And the best part is that you won’t even need to know English as your main language. The strong AI behind Spin Rewriter will create articles in perfect english in minutes. Check it out and tell me what you think.

    You can reply to this email to ask for samples of websiteswhere we publish the content written by Spin Rewritter

Leave a Reply

Your email address will not be published. Required fields are marked *