Giter Site home page Giter Site logo

mssql-studing-with-azuredatastudio's Introduction

MSSQL TSQL Management Studing

—— With Azure Data Studio

尝试使用新的MSSQL良好支持编辑器来进行可视化的数据库日常事务管理,并同时学习从前未曾接触的深度领域


内容&灵感来源:

- MS Learns


Samples

/* 各库所有元素视图 */
select * from sys.sysobject

/* 所辖各数据库文件空间占用情况 */
exec sp_MSforeachdb "EXEC sp_spaceused "

/* 索引优化建议官方引申模板 */
select
  CONVERT (varchar(30), getdate(), 126) AS runtime,
  db_name(d.database_id) dbname, object_name(d.object_id) tablename, d.index_handle,
  d.equality_columns, d.inequality_columns, d.included_columns, d.statement as fully_qualified_object,
  -- 根据平均时间耗费与索引添加后的影响,计算评估排序
  CONVERT (decimal (28, 1), gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) ) AS improvement_measure,
  -- 参考索引优化语句,注意应用前进行合并项考察
  'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
    + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'  -- 相比官方示例多在索引名中添加了表名
    + ' ON ' + d.statement
    + ' (' + ISNULL (d.equality_columns,'')
      + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
      + ISNULL (d.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
  , gs.*, d.database_id, d.[object_id]
-- into #temp1
from sys.dm_db_missing_index_groups g
    join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
    join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle
where  d.database_id =  d.database_id and d.object_id =  d.object_id
    --    and object_name(d.object_id) like '%'
    AND gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) > 10
ORDER BY improvement_measure DESC

Cooperated by

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.