[MSSQL]分享一个SQLSERVER脚本(计算数据库中各个表的数据量和每
分享一个SQLSERVER脚本(计算数据库中各个表的数据量和每行记录所占用空间) 很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间 这里共享一个脚本 CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR ( 500 ) , rowsinfo BIGINT , reserved
分享一个SQLSERVER脚本(计算数据库中各个表的数据量和每行记录所占用空间)
很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间
这里共享一个脚本
<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo ( nameinfo </span><span>VARCHAR</span>(<span><strong>500</strong></span><span>) , rowsinfo </span><span>BIGINT</span><span> , reserved </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , datainfo </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , index_size </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , unused </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ) </span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span><strong>255</strong></span><span>); </span><span>DECLARE</span> Info_cursor <span>CURSOR</span> <span>FOR</span> <span>SELECT</span> <span>'</span><span>[</span><span>'</span> <span>+</span> <span>[</span><span>name</span><span>]</span> <span>+</span> <span>'</span><span>]</span><span>'</span> <span>FROM</span><span> sys.tables </span><span>WHERE</span> type <span>=</span> <span>'</span><span>U</span><span>'</span><span>; </span><span>OPEN</span><span> Info_cursor </span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> Info_cursor <span>INTO</span> <span>@tablename</span> <span>WHILE</span> <span><strong>@@FETCH_STATUS</strong></span> <span>=</span> <span><strong>0</strong></span> <span>BEGIN</span> <span>INSERT</span> <span>INTO</span><span> #tablespaceinfo </span><span>EXEC</span> sp_spaceused <span>@tablename</span> <span>FETCH</span> <span>NEXT</span> <span>FROM</span><span> Info_cursor </span><span>INTO</span> <span>@tablename</span> <span>END</span> <span>CLOSE</span><span> Info_cursor </span><span>DEALLOCATE</span><span> Info_cursor </span><span>--</span><span>创建临时表</span> <span>CREATE</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( TableName </span><span>VARCHAR</span>(<span><strong>50</strong></span><span>) , DataInfo </span><span>BIGINT</span><span> , RowsInfo </span><span>BIGINT</span><span> , Spaceperrow </span><span>AS</span> ( <span>CASE</span><span> RowsInfo </span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span> <span>ELSE</span> DataInfo <span>/</span><span> RowsInfo </span><span>END</span><span> ) PERSISTED ) </span><span>--</span><span>插入数据到临时表</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( </span><span>[</span><span>TableName</span><span>]</span><span> , </span><span>[</span><span>DataInfo</span><span>]</span><span> , </span><span>[</span><span>RowsInfo</span><span>]</span><span> ) </span><span>SELECT</span> <span>[</span><span>nameinfo</span><span>]</span><span> , </span><span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>datainfo</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) <span>AS</span> <span>'</span><span>datainfo</span><span>'</span><span> , </span><span>[</span><span>rowsinfo</span><span>]</span> <span>FROM</span><span> #tablespaceinfo </span><span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(reserved, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>--</span><span>汇总记录</span> <span>SELECT</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>*</span><span> , </span><span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>Spaceperrow</span><span>]</span> <span>AS</span> <span>'</span><span>每行记录大概占用空间(KB)</span><span>'</span> <span>FROM</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>AS</span><span> tbspinfo , </span><span>[</span><span>#tmptb</span><span>]</span> <span>AS</span><span> tmptb </span><span>WHERE</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>nameinfo</span><span>]</span> <span>=</span> <span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>TableName</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>reserved</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span>
注意:使用之前要计算哪个数据库的记录,请先USE一下要统计表记录数的那个数据库!!
工作中遇到的问题
可以说我在实际的工作中 ,在100个问题中有90个都会先用到这个脚本
这里举一个我本人工作中遇到的一些问题
问题一:
程序员反映数据库查询慢,5分钟还没有出结果
我先用这个脚本看一下这个表有多少记录,大概有1000w+条数据
然后在本地的SSMS里查询,确实也是大概4分钟的样子才出来数据,看一下执行计划,发现查询能使用到索引
看一下数据库的压力,并不是很大,我跟会不会跟数据量有关系呢?
程序员要查询的结果条数是500条数据,业务表是做了分区的,按道理应该不会慢成这样。。。
后来我再看一下共享出来的那个脚本的结果,发现查询的结果大小=每行记录的大小*记录数
要查询大概500MB的数据,再传到客户端,不慢才怪
为什麽查询出的结果这么大?
主要是有几个大字段:例如:二进制字段和NVARCHAR(MAX)
并且时间范围跨度比较大
马上叫程序员改一下查询的语句,由于是entity framework程序,怎麽改我就不太清楚了,主要是不必要的字段就不查询处理并且缩小时间范围
问题二:
还有一些问题也需要知道每行记录的大小,例如删除表的历史数据,QA说要保留2013年之前的数据,你需要查出保留的数据或者2013年之前的数据占用多少G空间
再结合当前服务器的磁盘可用空间,来评估删除的数据是否太多或者太少
那么流程是:先查出2013年之前的记录数有多少-》计算表的总记录数-》计算表的大小-》手工计算每行记录的大小-》乘以2013年之前的记录数
如果没有每行记录数这个字段,那么你手工计算,是不是效率就变慢了???
问题三:
导数据的时候,你想知道当前已经导了多少数据了,那么执行一下这个脚本就可以了,这个脚本基本不会被阻塞
很快就能查出结果
脚本的计算方法
方法一
实际上利用的就是数据行大小的信息除以记录数
<span>CASE</span><span> RowsInfo </span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span> <span>ELSE</span> DataInfo <span>/</span> RowsInfo
方法二
<span>SELECT</span> <span>AVG</span>(<span>DATALENGTH</span>(C0))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C1))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C2))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C3)) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>TB106</span><span>]</span>
说一下两种方法的区别
第一种方法是效率高,当表有上亿条记录的时候,如果你使用第二种方法执行AVG(DATALENGTH(C0))是很慢的,因为SQLSERVER要统计字段大小信息
可能十几分钟都出不来结果
当然,第一种方法也有一些缺陷,就是当表的记录数少的时候,统计出来的每行记录占用空间是不准确的
因为datainfo这个值是以数据页大小为单位的,因为就算表只有一条记录,那么也会占用一个数据页(8KB)
那么当8KB/1 =8KB,一条记录肯定不会是8KB大小的,所以记录少的时候会不准确
但是当记录数很多的时候,就准确了
看一下TB106这个表统计出来的结果值
<span>SELECT</span> <span>AVG</span>(<span>DATALENGTH</span>(C0))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C1))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C2))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C3)) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>TB106</span><span>]</span>
可以看到是比较准确的
注意:
无论方法一还是方法二都不包括索引所占用的空间 !!
总结
大家平时一定会想:究竟DBA有什么作用?
在这里就给大家一个例子了,在工作中,程序员是不会关心他要查询的数据的大小的,他不管三七二十一只要把数据select出来就行了,然后收工
DBA这里就要解决数据查询不出来的问题,一般的程序员觉得查询500条数据是很少的,根本不会关心表设计,表的字段的数据类型
当工作越来越多,开发任务越来越重的时候更是这样
所以本人觉得DBA这个角色还是比较重要的o(∩_∩)o
如有不对的地方,欢迎大家拍砖o(∩_∩)o
2014-7-7 脚本bug修复
由于算出来每行记录的精度有问题,我又对脚本的精度进行了改进
<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo ( nameinfo </span><span>VARCHAR</span>(<span><strong>500</strong></span><span>) , rowsinfo </span><span>BIGINT</span><span> , reserved </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , datainfo </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , index_size </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , unused </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ) </span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span><strong>255</strong></span><span>); </span><span>DECLARE</span> Info_cursor <span>CURSOR</span> <span>FOR</span> <span>SELECT</span> <span>'</span><span>[</span><span>'</span> <span>+</span> <span>[</span><span>name</span><span>]</span> <span>+</span> <span>'</span><span>]</span><span>'</span> <span>FROM</span><span> sys.tables </span><span>WHERE</span> type <span>=</span> <span>'</span><span>U</span><span>'</span><span>; </span><span>OPEN</span><span> Info_cursor </span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> Info_cursor <span>INTO</span> <span>@tablename</span> <span>WHILE</span> <span><strong>@@FETCH_STATUS</strong></span> <span>=</span> <span><strong>0</strong></span> <span>BEGIN</span> <span>INSERT</span> <span>INTO</span><span> #tablespaceinfo </span><span>EXEC</span> sp_spaceused <span>@tablename</span> <span>FETCH</span> <span>NEXT</span> <span>FROM</span><span> Info_cursor </span><span>INTO</span> <span>@tablename</span> <span>END</span> <span>CLOSE</span><span> Info_cursor </span><span>DEALLOCATE</span><span> Info_cursor </span><span>--</span><span>创建临时表</span> <span>CREATE</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( TableName </span><span>VARCHAR</span>(<span><strong>50</strong></span><span>) , DataInfo </span><span>BIGINT</span><span> , RowsInfo </span><span>BIGINT</span><span> , Spaceperrow </span><span>AS</span> ( <span>CASE</span><span> RowsInfo </span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span> <span>ELSE</span> <span>CAST</span>(DataInfo <span>AS</span> <span>decimal</span>(<span><strong>18</strong></span>,<span><strong>2</strong></span>))<span>/</span><span>CAST</span>(RowsInfo <span>AS</span> <span>decimal</span>(<span><strong>18</strong></span>,<span><strong>2</strong></span><span>)) </span><span>END</span><span> ) PERSISTED ) </span><span>--</span><span>插入数据到临时表</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( </span><span>[</span><span>TableName</span><span>]</span><span> , </span><span>[</span><span>DataInfo</span><span>]</span><span> , </span><span>[</span><span>RowsInfo</span><span>]</span><span> ) </span><span>SELECT</span> <span>[</span><span>nameinfo</span><span>]</span><span> , </span><span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>datainfo</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) <span>AS</span> <span>'</span><span>datainfo</span><span>'</span><span> , </span><span>[</span><span>rowsinfo</span><span>]</span> <span>FROM</span><span> #tablespaceinfo </span><span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(reserved, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>--</span><span>汇总记录</span> <span>SELECT</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>*</span><span> , </span><span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>Spaceperrow</span><span>]</span> <span>AS</span> <span>'</span><span>每行记录大概占用空间(KB)</span><span>'</span> <span>FROM</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>AS</span><span> tbspinfo , </span><span>[</span><span>#tmptb</span><span>]</span> <span>AS</span><span> tmptb </span><span>WHERE</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>nameinfo</span><span>]</span> <span>=</span> <span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>TableName</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>reserved</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span>

Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

AI Hentai Generator
Menjana ai hentai secara percuma.

Artikel Panas

Alat panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas

Untuk objek dengan nama yang sama yang telah wujud dalam pangkalan data SQL Server, langkah berikut perlu diambil: Sahkan jenis objek (jadual, paparan, prosedur tersimpan). IF NOT EXISTS boleh digunakan untuk melangkau penciptaan jika objek kosong. Jika objek mempunyai data, gunakan nama lain atau ubah suai struktur. Gunakan DROP untuk memadam objek sedia ada (berhati-hati, sandaran disyorkan). Semak perubahan skema untuk memastikan tiada rujukan kepada objek yang dipadam atau dinamakan semula.

Langkah import adalah seperti berikut: Salin fail MDF ke direktori data SQL Server (biasanya C:\Program Files\Microsoft SQL Server\MSSQL\DATA). Dalam SQL Server Management Studio (SSMS), buka pangkalan data dan pilih Lampirkan. Klik butang Tambah dan pilih fail MDF. Sahkan nama pangkalan data dan klik butang OK.

Apabila perkhidmatan SQL Server gagal dimulakan, berikut ialah beberapa langkah untuk diselesaikan: Semak log ralat untuk menentukan punca. Pastikan akaun perkhidmatan mempunyai kebenaran untuk memulakan perkhidmatan. Semak sama ada perkhidmatan pergantungan sedang berjalan. Lumpuhkan perisian antivirus. Membaiki pemasangan SQL Server. Jika pembaikan tidak berjaya, pasang semula SQL Server.

Untuk melihat nombor port SQL Server: Buka SSMS dan sambung ke pelayan. Cari nama pelayan dalam Object Explorer, klik kanan padanya dan pilih Properties. Dalam tab Sambungan, lihat medan Port TCP.

Jika anda secara tidak sengaja memadamkan pangkalan data SQL Server, anda boleh mengambil langkah-langkah berikut untuk memulihkan: hentikan aktiviti pangkalan data periksa log pangkalan data: pulihkan daripada sandaran menggunakan DBCC; alatan parti. Sila sandarkan pangkalan data anda dengan kerap dan dayakan pengelogan transaksi untuk mengelakkan kehilangan data.

Fail pangkalan data SQL Server biasanya disimpan di lokasi lalai berikut: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data Lokasi fail pangkalan data boleh disesuaikan dengan mengubah suai laluan fail pangkalan data tetapan.

Saya menangis hingga mati. Dunia sedang membina model besar. Data di Internet tidak mencukupi. Model latihan kelihatan seperti "The Hunger Games", dan penyelidik AI di seluruh dunia bimbang tentang cara memberi makan data ini kepada pemakan yang rakus. Masalah ini amat ketara dalam tugas berbilang modal. Pada masa mereka mengalami kerugian, pasukan pemula dari Jabatan Universiti Renmin China menggunakan model baharu mereka sendiri untuk menjadi yang pertama di China untuk menjadikan "suapan data yang dijana model itu sendiri" menjadi kenyataan. Selain itu, ia merupakan pendekatan serampang dua mata dari segi pemahaman dan sisi penjanaan Kedua-dua pihak boleh menjana data baharu berbilang modal yang berkualiti tinggi dan memberikan maklum balas data kepada model itu sendiri. Apakah model? Awaker 1.0, model berbilang modal besar yang baru sahaja muncul di Forum Zhongguancun. Siapa pasukan itu? Enjin Sophon. Diasaskan oleh Gao Yizhao, pelajar kedoktoran di Sekolah Kecerdasan Buatan Hillhouse Universiti Renmin.

Jika pemasangan SQL Server gagal, anda boleh membersihkannya dengan mengikuti langkah berikut: Nyahpasang SQL Server Padam kekunci pendaftaran Padam fail dan folder Mulakan semula komputer
