技术标签: 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/
文章浏览阅读1.9k次。formData1.创建一个formData对象var fd = new FormData(‘form表单’);(创建formdtata对象的小括号里面,就是需要一个form表单dom对象)。2.往fd对象中添加对象fd.append(‘sex’,‘男’);3.formData里面就会有form表单中 有name属性的这些标签的取值。//键值对形式console.log(fd.ge...
文章浏览阅读244次。文章来源:【公众号:云加社区】目录简介整体生态工作原理Metric 指标PromQLGrafana 可视化监控告警简介Prometheus 是一个开源的完整监控解决方案,本文将从指标抓取到查询及可视化展示,以及最后的监控告警,对 Prometheus 做一个基本的认识。Prometheus 是古希腊神话里泰坦族的一名神明,名字的意思是“先见之明”,下图中是 Promet..._dtm prometheus
文章浏览阅读900次,点赞15次,收藏6次。对数据序列{6,8,7,9,0,1,3,2,4,5},请分别用快速排序算法和直接选择排序设计算法对数据序列进行排序;
文章浏览阅读597次。/*找出10000以内的同构数同构数 376*376=141376思路:1、输入一个数num 先判断是几位数。记住数位length。 2、然后算它(num)的平方, square。 3、取square的后length位的数值temp 4、temp与num相等,则是同构数。*/#include <iostream>#include &..._小于10000的同构数
文章浏览阅读5.1k次,点赞3次,收藏26次。写了很久的语音呼叫功能、调用在线语音合成的调用系统自带的;现在客户又要求搞网页版的语音呼叫还是不带联网的。客户太难伺候了详细使用请参考 【web语音API】完整代码<!DOCTYPE><html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN"><head><title>网页文字转语音</title><meta http-equiv="Content-Typ._js tts
文章浏览阅读62次。System.out.println("100个和尚吃了100个馒头 ,100和尚有大和尚和小和尚,一个大和尚能吃3馒头,三个小和尚吃1个馒头,问大和尚和小和尚有多少个?System.out.println("大和尚有"+i+"个人");System.out.println("小和尚有"+j+"个人");System.out.println("查看答案请按回车键");
文章浏览阅读47次。定义:允许将对象组成树形结构来表现 “整体/部分” 层次结构。组合能让客户以一致的方式处理个别对象及对象组合。说白了,就是类似于树形结构。 只是它要求子节点和父节点都具备统一的接口。类图如下:示例如下:比如我们常见的电脑上的目录,目录下面有文件夹,也有文件,然后文件夹里面还有文件及文件夹。这样一层层形成了树形结构。示例代码如下:#include <iostream>#include <stdio.h>#include "string"#includ..
文章浏览阅读1.9w次,点赞26次,收藏185次。目录一.请简述下什么是kotlin?它有什么特性?二.Kotlin 中注解 @JvmOverloads 的作用?三.Kotlin中的MutableList与List有什么区别?四.kotlin实现单例的几种方式?五. kotlin中关键字data的理解?相对于普通的类有哪些特点?六.什么是委托属性?简单说一下应用场景?七.kotlin中with、run、apply、let函数的区别?一般用于什么场景?八.kotlin中Unit的应用以及和Java中void的区别?九.Ko_kotlin面试题
文章浏览阅读2.8k次。有这个想法一方面是确实很多时候会记不得一些缩写是什么意思。另外也是受 http://blog.csdn.net/lin453701006/article/details/52797415这篇博客的启发,本文主要用于自己记忆 内容主要整理自http://blog.sina.com.cn/s/blog_520811730101hmj9.html http://blog.csdn.net/feix_反量化 英文缩写
文章浏览阅读7.3k次,点赞6次,收藏36次。超级简单的Python爬虫入门教程(非常详细),通俗易懂,看一遍就会了_爬虫python入门
文章浏览阅读1.2k次。您的代码存在一些问题。首先,您在此处显示的两个模型是not等效的:尽管您将scikit-learn LogisticRegression设置为fit_intercept=True(这是默认设置),但您并没有这样做statsmodels一;来自statsmodels docs:默认情况下不包括拦截器,用户应添加。参见statsmodels.tools.add_constant。另一个问题是,尽管您处..._sm fit(method
文章浏览阅读518次。一、sfml官网下载32位的版本 一样的设置,64位的版本我没有成功,用不了。二、三、四以下这些内容拷贝过去:sfml-graphics-d.libsfml-window-d.libsfml-system-d.libsfml-audio-d.lib..._vsllfqm