技术标签: python java mysql 数据库 大数据
Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.
数据库部署是关键任务,可能会影响生产性能。 在本文中,我们将介绍一些与性能相关的数据库部署最佳实践。
In this article, I’ll cover several aspects of Microsoft SQL Server databases deployment process including :
在本文中,我将介绍Microsoft SQL Server数据库部署过程的几个方面, 包括 :
The first thing we should do is to collect some fundamental information about this new database to be familiar with the business logic of it, usage, capacity plan, etc. The following are some examples
我们应该做的第一件事是收集有关此新数据库的一些基本信息,以熟悉该数据库的业务逻辑,用法,容量计划等。 以下是一些例子
These provide a good set of example checks to help conclude the Database size, usage, requirements, configuration etc
这些提供了一组很好的示例检查,以帮助总结数据库的大小,用法,要求,配置等
Before we start we should do a pre-deployment analysis of potential performance issues including:
在开始之前,我们应该对潜在的性能问题进行部署前分析,其中包括:
The following are some example
以下是一些示例
SELECT TABLE_NAME,
COLUMN_NAME ,
DATA_TYPE + ' (MAX)' AS DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS INFO INNER JOIN Sys.Tables T
ON Info.TABLE_NAME = T.name
WHERE Info.CHARACTER_MAXIMUM_LENGTH = '-1'
AND DATA_TYPE NOT IN ( 'text', 'Image', 'Ntext', 'FILESTREAM', 'Xml',
'varbinary' )
ORDER BY TABLE_NAME
WITH schema_design AS
(
SELECT
info.TABLE_CATALOG ,
--SCHEMA_NAME(schema_id) AS [SCHEMA_Name],
info.TABLE_SCHEMA as TABLE_SCHEMA,
--'['+SCHEMA_NAME(schema_id)+'].['+T.name+']' as TABLE_NAME,
Info.TABLE_NAME,
info.COLUMN_NAME,
INfo.DATA_TYPE,
Info.COLLATION_NAME,
INfo.CHARACTER_MAXIMUM_LENGTH,
INfo.CHARACTER_OCTET_LENGTH,
C.is_computed,
C.is_filestream
,C.is_identity,
COLUMNPROPERTY(object_id(TABLE_NAME),
COLUMN_NAME, 'IsIdentity')AS IsIdentity,
COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsPrimaryKey')AS IsPrimaryKey
from Sys.Columns as C
inner join INFORMATION_SCHEMA.COLUMNS info
on info.TABLE_NAME = OBJECT_NAME(C.Object_Id)
and Info.COLUMN_NAME = C.name
--Select Distinct Info.Table_Name,Info.TABLE_CATALOG from INFORMATION_SCHEMA.COLUMNS info
Inner Join Sys.Tables T
On Info.TABLE_NAME = T.name
)
SELECT * FROM schema_design
WHERE DATA_TYPE IN ('text' , 'Image','Ntext','FILESTREAM','Xml','varbinary','sql_variant')
ORDER BY TABLE_NAME
Uniqueidentifier Columns AS primary key: is not preferred for many reasons
Uniqueidentifier Columns AS主键:由于许多原因,不被首选
For better results, consider using integer identity columns. Or at least newsequentialid() instead of a Primary Key on GUID
为了获得更好的结果,请考虑使用整数标识列。 至少是newsequentialid()而不是GUID上的主键
WITH CTE
AS ( SELECT S.name AS [Table Name] ,
c.name 'Column Name' ,
t.name 'Data type' ,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
INNER JOIN sys.tables S ON S.object_id = c.object_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
)
SELECT *
FROM CTE
WHERE [Data type] = 'uniqueidentifier'
AND CTE.[Primary Key] = 1;
The maximum key length is 900 bytes. The index has a maximum length of 1200 bytes. For some combination of large values, the insert/update operation will fail
最大密钥长度为900字节。 索引的最大长度为1200个字节。 对于大值的某些组合,插入/更新操作将失败
SELECT TABLE_NAME ,
COLUMN_NAME ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS INFO
INNER JOIN Sys.tables T ON INFO.TABLE_NAME = T.Name
WHERE Info.CHARACTER_MAXIMUM_LENGTH > '256'
AND Data_type NOT IN ( 'text', 'Image', 'Ntext', 'FILESTREAM', 'Xml',
'varbinary' )
ORDER BY CHARACTER_MAXIMUM_LENGTH DESC
Attached is a Stored Procedure “Check_Heap_Tables” and this SQL Server Stored Procedure will generate a T-SQL script for each Database and Table which is Heap Table and it will print out the required clustered index to be created for highlighted tables.
附加的是存储过程“ Check_Heap_Tables ” ,此SQL Server存储过程将为每个数据库和表(即堆表)生成一个T-SQL脚本,并将打印出要为突出显示的表创建的聚簇索引。
Use MSDatabase
Go
Exec dbo.[Check_Heap_Tables]
In this second phase our main focus in the most used stored procedures that we should execute it on 200 concurrent user minimum and 1000 concurrent user maximum for the purpose of stress testing the Application Level Stored Procedures for performance testing.
在第二阶段,我们主要关注最常用的存储过程,我们应该在200个并发用户数和1000个并发用户数上执行它,以便对应用程序级存储过程进行压力测试以进行性能测试。
We can do this process by utilizing any stress tool, but we should fill the tables by at least 1 million of rows. In our case we’ll use ApexSQL Generate to help us highlight any potential issues via the query execution plan
我们可以通过使用任何压力工具来完成此过程,但我们应该至少在表中填充100万行。 在我们的案例中,我们将使用ApexSQL Generate通过查询执行计划帮助我们突出显示任何潜在的问题
Although there are multiple ways to tune-up TSQL some examples given below:
尽管有多种方法可以调整TSQL,但下面给出了一些示例:
Most recently, many applications use Entity Framework at the application layer to access the database. This Framework does not allow the queries to be written directly; instead it generates queries on its own as required by the application and business logic based on the underlying tables. So, the database does not have the business logic inside the SQL Server.
最近,许多应用程序在应用程序层使用实体框架来访问数据库。 该框架不允许直接编写查询。 相反,它根据基础表根据应用程序和业务逻辑的要求自行生成查询。 因此,数据库在SQL Server内部没有业务逻辑。
For optimizing and stress testing the code we need to capture the business logic code by running stress test scenarios for like 200~300 concurrent users. And ultimately able to capture poorly written or worst performing TSQL.
为了优化和压力测试代码,我们需要通过为200〜300个并发用户运行压力测试方案来捕获业务逻辑代码。 最终能够捕获写得不好或性能最差的TSQL。
Eventually, the goal here is to get the bad TSQL in hand and optimize it and give it back to the application team so that they can integrate it and merge inside the application for optimizing the business by converting the queries into SQL Server Custom Written Stored Procedures.
最终,这里的目标是掌握不良的TSQL并对其进行优化并将其返回给应用程序团队,以便他们可以将其集成并合并到应用程序内部,以通过将查询转换为SQL Server自定义书面存储过程来优化业务。 。
After all of the stress testing performed on the database for potential problematic Stored Procedures and T-SQL queries, SQL Server engine will save the required missing indexes in the system tables which we can now query.
在对数据库执行了所有潜在的有问题的存储过程和T-SQL查询的压力测试之后,SQL Server引擎会将所需的缺失索引保存在我们现在可以查询的系统表中。
After analyzing the Missing Indexes from the System Stored Procedures, we can start to create the appropriate missing indexes on the tables. This process should be done only by experienced DatabaseAs and in collaboration with the Development team to enhance the performance of the poorly performing quires and Stored Procedures.
在分析了系统存储过程中的缺失索引之后,我们可以开始在表上创建适当的缺失索引。 此过程仅应由经验丰富的DatabaseA并与开发团队合作完成,以提高性能不佳的查询和存储过程的性能。
SELECT B.name AS DatabaseNAme,D.database_id,D.* ,
s.avg_total_user_cost ,
s.avg_user_impact ,
s.last_user_seek ,
s.last_user_scan ,
s.unique_compiles ,
'CREATE INDEX [IX_' + OBJECT_NAME(d.object_id, d.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns, ''), ', ', '_'),
'[', ''), ']', '')
+ CASE WHEN d.equality_columns IS NOT NULL
AND d.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality_columns, ''), ', ',
'_'), '[', ''), ']', '') + ']'
+ ' 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
+ ')', '')
+ ' WITH(FILLFACTOR = 80 , DATA_COMPRESSION = PAGE) ' + CHAR(10)
+ CHAR(13) + 'PRINT ''Index '
+ CONVERT(VARCHAR(10), ROW_NUMBER() OVER ( ORDER BY avg_user_impact DESC ))
+ ' [IX_' + OBJECT_NAME(d.object_id, d.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns, ''), ', ', '_'),
'[', ''), ']', '')
+ CASE WHEN d.equality_columns IS NOT NULL
AND d.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality_columns, ''), ', ',
'_'), '[', ''), ']', '') + '] '
+ ' created '' + CONVERT(VARCHAR(103),GETDATE())' + CHAR(10) + CHAR(13)
+ ' Go' + CHAR(10) + CHAR(13) AS Create_Statement
FROM sys.dm_db_missing_index_group_stats s ,
sys.dm_db_missing_index_groups g ,
sys.dm_db_missing_index_details d
INNER JOIN Sys.databases AS B
ON d.database_id = B.database_id
WHERE s.group_handle = g.index_group_handle
AND d.index_handle = g.index_handle
AND s.avg_user_impact >= 90
AND D.database_id > 4 AND B.NAME <> 'distribution'
ORDER BY name,avg_user_impact DESC
go
Be aware that your production environment is a restricted area and never deploy anything without a clear and well documented assessment process, to ensure the best performance of your production database.
请注意,您的生产环境是受限制的区域,没有清晰且记录良好的评估流程,切勿部署任何东西,以确保生产数据库的最佳性能。
翻译自: https://www.sqlshack.com/microsoft-sql-server-database-deployment-process/
文章浏览阅读87次。 高并发下的Nginx性能优化实战、解读Nginx的核心知识、掌握nginx核心原理?通过本期课程将解答我们的疑惑。_随着 nginx 发展,高并发、轻量的优势,近几年
文章浏览阅读1.1k次。docker启动redis命令_linux docker 重启 redis
文章浏览阅读325次。插头DP小结_dp插头模型
文章浏览阅读3.5k次。关于测试工作效率低的一些思考和改进方法引子 汇总统计了一下项目组近期测试项目实际工作量与基线工作量的对比,发现一个严重问题。就是工作效率特别低下。下面简单列举一下几个项目预期工作量和实际工作量以及时间耗费严重的地方、项目简要背景。 1、B版本测试。版本预期工作量15人天,实际耗费工作量在30人天。更为严重的是测试人员并没有因为测试周期延长和工作量投入加大而测试的更轻松,反而是测试期..._测试人员不足与改进
文章浏览阅读173次。级联样式表 CSS-难以成熟 (CSS — Difficult to maturation)Unlike software, the CSS specifications are developed by successive versions, which would allow a browser to refer to a particular version. CSS was devel..._级联样式表是哪年产生的
文章浏览阅读1.7k次。目录批处理语句1、批处理语句简介示例一:示例二:存储过程一、什么是存储过程1、存储过程的简介2、存储过程包含的内容3、存储过程的优点4、存储过程的分类系统存储过程:用户定义存储过程5、常用的系统储存过程(1)一般常用的存储过程(2)xp_cmdshell二、创建存储过程1、定义存储过程的语法2、不带参数的存储过程3、带参数..._sql的批处理
文章浏览阅读1.4k次。代码 List<book> list = new ArrayList<book>(); Document doc = new DOCUntil().getDocument("src/ww/t/qp/books.xml"); Element root = doc.getDocumentElement(); NodeList books = root.getElementsByTagName("book"); for _com.sun.org.apache.xerces.internal.dom.deferredtextimpl cannot be cast to or
文章浏览阅读2.9k次。计算机网络定义: 一些互相连接的,自治的计算机或者智能硬件设备集合信息是如何交换的?1、电路交换(电话)线路被某个终端占用后不会被释放 除非主动释放2、分组交换分组交换是 把大数据拆分成小数据进行分组传输 提高传输效率计算机上的数据是突发的,所以不采用电路交换,而采用分组交换3、ISP(internet service provider)网络服务提供商终端通过交换机连接一级ISP(国际性区域)之间互相连接 某一条线路断后可以通过其他设备进行通信二级ISP(国家/区域性规模)与一级相互连接三级ISP(本地区域
文章浏览阅读3.5k次,点赞12次,收藏46次。/** * @brief * @note * @param None * @retval None * @author PWH * @date 2021/4 */int32_t GT20L16S1Y_Get_Addr_Ascii_7x8(char *asciiCode){ if (*asciiCode >= ' ' && *asciiCode <= '~') { return 0x66C0 + (*asciiCode - ._gt20l16s1y
文章浏览阅读1.3k次。最近我的ETH交易里面出现了一种情况,交易虽然返回了hash,但是却迟迟未被确认,连在区块浏览器上也找不到对应的pending交易, 但是通过节点api调用hash获取记忆记录却有数据返回。这种情况问了人也查了资料,最后得出的结果是,交易可能在矿池,但是却没有被矿工打包。于是想到了imtoken钱包的加速交易内容。说到加速交易,不得不说的就是ETH交易内重要的一个内容, nonce。nonc..._ethers获取交易nonce
文章浏览阅读7.6k次。在Android中我们可以用visibility来控制控件的显示和隐藏,那在Flutter中我们怎么控制呢?其实,在Flutter中控制Widget显示和隐藏有3中方法:不过3种方法的核心思想都是根据变量的值去判断的,所以先定义一个变量:bool visible = true;变量的值可以在事件中去控制,比如: onPressed: () { setS..._flutter判断控制是否被遮住
文章浏览阅读673次。有没有师兄师姐有meganv2.04以上的版本小弟只有低版本的 需要高版本运行一下有偿!_megan v2.04