[MSSQL] share an SQL server script to calculate the data volume of each table in the database and

Share an SQLSERVER script (calculate the data volume of each table in the database and the space occupied by each row of Records) in many cases, we need to calculate the data volume of each table in the database and the space occupied by each record. Here we share a script CREATETABLE # tablespaceinfo (nameinfoVARCHAR (500), rowsinfoBIGINT, reserved

Share an SQLSERVER script (calculate the data volume of each table in the database and the space occupied by each row of Records) in many cases, we need to calculate the data volume of each TABLE in the database and the space occupied by each record. Here we share a script create table # tablespaceinfo (nameinfo VARCHAR (500), rowsinfo BIGINT, reserved

Share an SQLSERVER script (calculate the data volume of each table in the database and the space occupied by each row of Records)

In many cases, we need to calculate the data volume of each table in the database and the space occupied by each row of records.

Share a script here

Create table # tablespaceinfo (nameinfo VARCHAR (500), Rowsinfo BIGINT, reserved VARCHAR (20), Datainfo VARCHAR (20), Index_size VARCHAR (20), Unused VARCHAR (20) DECLARE @ tablename VARCHAR (255); DECLARE Info_cursor cursorfor select '[' + [name] + ']' FROM sys. tables WHERE type = 'U'; OPEN Info_cursor fetch next from Info_cursor INTO @ tablename WHILE@ FETCH_STATUS=0Begin insert into # tablespaceinfo EXEC sp_spaceused @ tablename fetch next from Info_cursor INTO @ tablename end close Info_cursor DEALLOCATE Info_cursor -- CREATE a temporary table create table [# tmptb] (TableName VARCHAR (50), DataInfo BIGINT, RowsInfo BIGINT, Spaceperrow AS (CASE RowsInfo WHEN0THEN0ELSE DataInfo/RowsInfo END) PERSISTED) -- INSERT data to the temporary table insert into [# tmptb] ([TableName], [DataInfo], [RowsInfo]) SELECT [nameinfo], CAST (REPLACE ([datainfo], 'kb', '') as bigint) AS 'datainfo', [rowsinfo] FROM # tablespaceinfo order by cast (REPLACE (reserved, 'kb', '') as int) DESC -- SELECT [tbspinfo]. *, [tmptb]. [Spaceperrow] AS 'space occupied by each record (KB) 'FROM [# tablespaceinfo] AS tbspinfo, [# tmptb] AS tmptbWHERE [tbspinfo]. [nameinfo] = [tmptb]. [TableName] order by cast (REPLACE ([tbspinfo]. [reserved], 'kb', '') as int) desc drop table [# tablespaceinfo] drop table [# tmptb]

Note: If you want to calculate the record of a database before using it, please USE the database that wants to calculate the number of records in the table first !!

Problems encountered at work

It can be said that in my actual work, 90 of the 100 questions will use this script first.

Here is a question I encountered during my work.

Question 1:

The programmer reported that the database query was slow and there was no result in 5 minutes.

I will first use this script to check the number of records in the table, which may contain more than 1000 million data records.

Then we can query the data in the local SSMS, which is about 4 minutes later. Let's take a look at the execution plan and find that the index can be used for the query.

Let's take a look at the pressure on the database. Isn't it related to the data volume?

The number of results to be queried by programmers is 500 data records, and the business table is partitioned. It should not be slowed down as follows...

Later, I looked at the result of the shared script and found that the query result size = the size of records in each row * Number of records

It takes no time to query about MB of data and upload it to the client.

Why is the query result so large?

There are several major fields: Binary fields and NVARCHAR (MAX)

And the time range is relatively large.

Ask the programmer to change the query statement immediately. Because it is an entity framework program, I am not quite clear about how to change it. The main reason is that the unnecessary fields are not queried and the time range is reduced.

Question 2:

We also need to know the size of records in each row. For example, we need to delete the historical data of a table. QA says we want to keep the data before January 1, 2013, you need to find out the reserved data or the amount of GB space occupied by the data before January 1, 2013.

Combined with the available disk space of the current server, we can evaluate whether there is too much or too little data to be deleted.

The process is: first, find out the number of records before January 1, 2013-"calculate the total number of records in the table-" Calculate the table size-"manually calculate the size of records in each row-" multiply by the number of records before January 1, 2013

If there is no field for the number of records in each row, will the efficiency become slower if you calculate it manually ???

Question 3:

If you want to know how much data has been imported during data import, You can execute this script and it will not be blocked.

The results will soon be available.

Script Calculation Method

Method 1

Actually, we use the data row size information divided by the number of records.

CASE RowsInfoWHEN 0 THEN 0ELSE DataInfo / RowsInfo

Method 2

SELECT AVG(DATALENGTH(C0))+AVG(DATALENGTH(C1))+AVG(DATALENGTH(C2))+AVG(DATALENGTH(C3)) FROM [dbo].[TB106]

Let's talk about the differences between the two methods.

The first method is high efficiency. When the table has hundreds of millions of records, it is very slow to use the second method to execute AVG (DATALENGTH (C0) Because SQLSERVER requiresStatisticsField Size Information

It may take 10 minutes to parse the results.

Of course, the first method also has some drawbacks, that is, when the number of records in the table is small, the space occupied by each row of records is inaccurate.

Because the value of datainfo is in the unit of data page size, even if the table only has one record, it will occupy one data page (8 KB)

So when 8 KB/1 = 8 KB, a record will certainly not be 8 KB, so when there are few records, it will be inaccurate

However, when the number of records is large, it is accurate.

Let's take a look at the result value from the table TB106.

SELECT AVG(DATALENGTH(C0))+AVG(DATALENGTH(C1))+AVG(DATALENGTH(C2))+AVG(DATALENGTH(C3)) FROM [dbo].[TB106]

It can be seen that it is relatively accurate

Note:

Method 1 and method 2 do not include the space occupied by the index !!

Summary

You will always think: what is the role of DBA?

Here is an example for everyone. At work, programmers do not care about the size of the data they want to query. He just needs to select the data, regardless of the size of the data he wants to query, then close the work

DBAs must solve the problem of data query failure. Generally, Programmers think that there are very few queries for 500 pieces of data, and they do not care about table design. The data type of table fields

This is especially true when there are more and more jobs and more development tasks.

Therefore, I think the DBA role is still an important o (role _ Role) o.

If anything is wrong, you are welcome to make a brick o

Script bug fixing

The accuracy of each row of records is incorrect. I have improved the accuracy of the script.

Create table # tablespaceinfo (nameinfo VARCHAR (500), Rowsinfo BIGINT, reserved VARCHAR (20), Datainfo VARCHAR (20), Index_size VARCHAR (20), Unused VARCHAR (20) DECLARE @ tablename VARCHAR (255); DECLARE Info_cursor cursorfor select '[' + [name] + ']' FROM sys. tables WHERE type = 'U'; OPEN Info_cursor fetch next from Info_cursor INTO @ tablename WHILE@ FETCH_STATUS=0Begin insert into # tablespaceinfo EXEC sp_spaceused @ tablename fetch next from Info_cursor INTO @ tablename end close Info_cursor DEALLOCATE Info_cursor -- CREATE a temporary table create table [# tmptb] (TableName VARCHAR (50), DataInfo BIGINT, RowsInfo BIGINT, Spaceperrow AS (CASE RowsInfo WHEN0THEN0Else cast (DataInfo AS decimal (18,2)/CAST (RowsInfo AS decimal (18,2) END) PERSISTED) -- INSERT data to the temporary table insert into [# tmptb] ([TableName], [DataInfo], [RowsInfo]) SELECT [nameinfo], CAST (REPLACE ([datainfo], 'kb', '') as bigint) AS 'datainfo', [rowsinfo] FROM # tablespaceinfo order by cast (REPLACE (reserved, 'kb', '') as int) DESC -- SELECT [tbspinfo]. *, [tmptb]. [Spaceperrow] AS 'space occupied by each record (KB) 'FROM [# tablespaceinfo] AS tbspinfo, [# tmptb] AS tmptbWHERE [tbspinfo]. [nameinfo] = [tmptb]. [TableName] order by cast (REPLACE ([tbspinfo]. [reserved], 'kb', '') as int) desc drop table [# tablespaceinfo] drop table [# tmptb]