SQL Server性能问题案例解析 (3)

今天的博客是SQL Server 性能问题解析系列的最后一个案例。这个案例背景是用户反映以下查询语句执行时间过长,执行时间为10秒以上(已经排除了blocking原因),希望将执行时间压缩到2秒以内。 declare @Company as varchar(10) set @Company = ‘a’ declare @SystemVersion as varchar(30) set @SystemVersion = ‘Total Ticketing Solution v2.1.3′ declare @LocationID_PSS as varchar (10) declare @UserCode_PSS as varchar (10) declare @ProductId_PSS as varchar (10) declare @TrxDateFr as datetime declare @TrxDateTo as datetime set @LocationID_PSS = null set @UserCode_PSS = null set @ProductId_PSS…

2

SQL Server性能问题案例解析 (2)

语句执行时间长是SQL Server 性能问题的一种典型表现形式。当运行一条语句所需要的CPU时间较长或者所需的内存资源较多时,我们往往需要对目标语句本身进行调优。通常情况下,我们可以通过更新统计信息,修改索引,使用语句执行计划的强制选择(使用Hint), 以及对于语句本身的修改来使得语句占用更少的CPU时间或者内存。 在对问题语句调优之前,我们需要得到这条语句的执行计划。除了通过SQL Server Management Studio上的 “Include Actual Execution Plan”这个图标外,我们可以通过SQL Server Profiler中Performance – Showplan Statistics Profile这个事件的捕捉来获得语句的执行计划。 另外在问题语句执行时通过下面的方式可以收集到较为直观和详细的执行计划和相关统计信息。   SET STATISTICS IO ON; SET STATISTICS TIME ON; SET STATISTICS PROFILE ON; GO –问题语句执行 GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; SET STATISTICS PROFILE OFF;   下面开始我们的案例分析。 在这个案例中,客户最初碰到的问题是SQL Server 进程CPU使用率很高。通过我们的分析定位发现了占用CPU时间最多的语句。在这篇博文中,我们会着重分析这条问题语句占用较高CPU资源的原因。 问题语句本身是对一个用户定义视图运行select * 语句,相应的constraint也较为简单。但是这个视图的定义较为复杂,对应的定义我们简化如下:…

0

SQL Server性能问题案例解析 (1)

今天的博客将分享一个死锁的案例。阅读本文之前,需要对SQL Server的锁,事务,隔离级别有基本的了解。网络中有很多文章,我就不在这里复述了。 SQL Server中有一个叫做deadlock monitor的线程,会定期去检测死锁。如果检测到死锁发生,deadlock monitor会选择一个session作为victim 终止,从而解决死锁。在排查死锁时,我们通常建议客户开启trace flag 1222并抓取sql server trace用于分析。当开启了trace flag1222, SQL Server会将死锁的相关信息打印到ERROLROG中,包含信息有死锁的victim session, 各个参与死锁的session, 以及这些session正在等待的资源和锁。但如果同一时刻有多个死锁发生,这些死锁的信息会混杂在一起,很难区分。这时候如果有SQL trace,会相对容易很多。 我们的案例就是这个情况—同时发生了很多死锁。首先我们要找出死锁中牵扯到的所有session,整理出一个deadlock框架。 这是sql trace的一个截图   我们可以看到,session 71和session 121都在等待资源6cd5adef219d。 打开 SQL Server ERRORLOG,我们可以得知,当时的deadlock victim session的隔离级别, session id和等待资源。 deadlock victim是session 71。     ERRORLOG里也记录了session 71当时在等待什么资源,以及当时资源的的owner。对于session 71,我们可以得出如下信息: session id :71 Process id: process107bdf4cf8 isolation level:read committed status=suspened wait resource: 6cd5adef219d Resource-list关键字记录了当时涉及的资源等待情况,source owner,resource…

0

SQL Server连接问题案例解析(3)

本文是SQL Server 连接问题案例解析系列的最后一个博文,在今天的案例中,除了分析Netmon日志,还会分享一些分析ODBC Trace的经验。 首先来介绍一下这个案例中客户遇到的问题: 客户在Linux服务器上使用UNIXODBC2.3.0将客户端连接到SQL Server数据库。当客户端连接运行在同一个实例中的非镜像数据库时,没有任何问题发生。但如果客户端连接镜像数据库,则会出现以下报错: client=n/a (n/a) unixODBC-2.3.0 64-bit /ab/home/misc/64/unixODBC/lib/libodbc.so ABINITIO(DB00001): Failed connecting to the database <ALGO_PROD>. ABINITIO(DB00001): ABINITIO(DB16000): ODBC Error ABINITIO(DB16000): SQLCODE: 20020 ABINITIO(DB16000): SQLSTATE: 08S01 ABINITIO(DB16000): MESSAGE: [unixODBC][FreeTDS][SQL Server]Bad token from the server: Datastream processing out of sync ABINITIO(DB16000): ODBC Error ABINITIO(DB16000): SQLCODE: 0 ABINITIO(DB16000): SQLSTATE: 08001 ABINITIO(DB16000): MESSAGE: [unixODBC][FreeTDS][SQL Server]Unable…

0

AzureML中的回归

在Microsoft Azure Machine Learning中提供了以下几种回归模型: 贝叶斯线性回归 Bayesian Linear Regression 提升决策树回归 Boosted Decision Tree Regression 决策森林回归 Decision Forest Regression 快速森林分位回归 Fast Forest Quantile Regression 线性回归 Linear Regression 神经网络回归 Neural Network Regression 有序回归 Ordinal Regression 泊松回归 Poisson Regression 那什么叫回归呢?在统计分析中,回归是研究一个随机变量Y对于另一个(X)或一组(X1, X2, …, Xk)变量的相依关系的统计分析方法。我们通常把X1, X2等叫做自变量,在机器学习里又叫做Feature(特征),而又把Y叫做因变量,也就是我们希望预测分析的部分。Y一般都是数值,是连续的,而在分类中我们预测的部分通常只有个别的几个值/类, 所以我们可以了解回归Regression与分类Classification的差别。在回归里,自变量可以是数值型的也可以是分类型的,而因变量是数值的。 回归模型就是构造一个从x到y的映射关系。最简单的回归情形是当单个因变量和单个自变量为线性关系时,也就是一元线性回归(Linear Regression),通常的模型就是Y=a+bX+ε, 即在二维坐标上是一条直线,a是直线的截距,而b为直线的斜率。ε为随机误差(通常假定随机误差的均值为0)。通过获取样本数据,我们可以把这些点画成散点图,然后求解对应的系数a和b的值,这样就可以用一条直线来代表这个数据模型。当有新的自变量值输入到模型,我们就可以通过这个模型计算出因变量值从而进行预测。线性回归当然也可以是多元的,就是有多个X,互相之间是独立的。除了线性回归,还有非线性回归,也就是曲线或者曲面的回归。比如我们可以通过S型函数来进行逻辑回归。而更一般的情况是多元的情况。比如贝叶斯线性回归假设随机变量的概率分布近似于高斯分布也就是正态分布来描述,通过最大后验概率来求解回归问题。而泊松回归则假设因变量Y是泊松分布,并假设它期望值的对数可以被未知参数的线性组合建模。 决策树本来是应用最广的分类算法之一。决策树可以表示成多个If-Else的规则。它其实是将空间用超平面进行划分的一种方法,每次分割的时候,都将当前的空间一分为二,比如说下面的决策树: 我们可以在二维坐标中画出来:  这样我们可以在一个待分类的样本实例进行决策的时候根据2个特征(x,y)的取值来划分到对应的一个叶子节点,得到分类结果。这就是决策树分类的过程。而提升(Boosting)是属于模型组合的方法之一,通过组合效果可以比单个模型更好。Boosting就是用弱分类器先分类,然后把上次分错的数据权重提高一点,然后再分类,这样最终得到的模型效果比较好。在决策树上运用这个思想就可以达到更好的效果。而回归树选择变量的标准用残差平方和。在决策树的根部残差平方和就是回归的残差平方和。然后选择一个特征,使得通过这个进行分类后的两部分的分别的残差平方和的和最小。然后在分叉的节点再利用这个准则进行分类,生长成一个树。而在这个基础上,我们还要进行剪枝,通过训练样本来进行评估,如果被减的树中得到的残差平方和缩小了,那么剪枝就是有效的,否则就不剪。在机器学习中,随机森林包含多个决策树, 输出的类别是由个别数输出的类别的众数而定。而决策森林结合了随机森林和随机子空间方法来建造决策树的集合,它引入了模型组合的另外一个重要方法,也就是Bagging。对于每个节点会随机选择m个特征,根据这m个特征计算其最佳分裂方式。这里每棵树的成长是不会剪枝的。 在统计里面,有序回归是一类回归分析用来预测一个有序变量的。有序变量可以这样理解:比如我们评价客户满意度从1分到9分,1到3代表”非常不满意“,4到5分表示”不满意“,6分到7分代表”满意“,8和9代表“非常满意“。这就是有序变量。 而人工神经网络回归可用于非线性回归,是一种大规模并行非线性系统,具有良好的非线性映射能力,强大的解决问题的能力和实际推广能力。它是一组算法来模拟大脑的工作。神经网络算法有很多种,最主要的是backpropagation,又叫多层感知器。通常有三层网络:   最左边的是输入层,中间是隐藏层,而右边是输出层。当然我们可以有更多的隐藏层。一般输入的节点数相当于独立自变量的数量。而输出节点数为因变量的数量。而隐藏层的节点数就比较复杂。在训练阶段,我们给网络喂一组样本。在向前传播过程中,在隐藏层和输出层的节点会计算一个加权的输入总和,然后用这个总和通过一个激活函数来计算它的输出。通常我们用S型函数,或者高斯,甚至线性函数来作为激活函数。当输出计算好了以后,向后传播的步骤就开始了。在这个阶段,算法会计算预测值跟实际值的误差。使用梯度下降的方法,它调节所有回传误差连接的权重。这些权重的调整能够减少下次的误差。通过一系列样本的列举,最终神经网络形成对应的模型。在这其中,有2个参数比较重要。一个是学习的效率(learning rate)。如果效率太低,那么列举就多,时间就长。而效率太高,可能很难找到局部极小值。另一个是关于隐藏节点数量。随着隐藏节点的数量增加,模型的精确度可增加,但却会造成过拟合。通常我们设定输入节点数的平方根作为起始值或者这个值在输入节点数和输出节点数之间比如(输入节点数+输出节点数)/3。通常只有在测试中才能调整到比较合适的值。

0

Storm介绍

Apache Storm是一个分布式的,容错的,开源的计算系统。它允许我们使用Hadoop来进行实时数据处理。Storm的解决方案还能保证数据都被处理,它有能力对首次处理不成功的数据进行重新处理。根据一些性能方面基准测试,Storm可以达到每个节点每秒处理超过百万的Tuple。 那么为什么要用HDInsight里的Storm呢?在HDInsight服务里的Apache Storm是一个整合在Azure环境里的托管的集群。它提供了几个主要好处: 作为一个托管服务,它能够达到三个9 (99.9%)的高可用性 它支持使用各种语言, Java, C$, Python等等。还可以混合语言,比如读的时候用Java,处理的时候用C#. 它使用了Trident的Java接口来创建Storm Topology,支持“有且唯一”的消息处理,事物性的数据存储持续化,和一组通用的流分析的操作。 它提供了内建的动态扩展特点, 对一个HDInsight的集群进行扩展不会影响执行Storm Tology。 Storm和其他Azure服务整合在一起,包括Event Hub,Azure Virtual Network, SQL 数据库, Blob storage, 和Document DB。可以用Azure  Virtual Network把多个HDInsight Cluster能力整合起来,创建使用HDInsight HBase或者Hadoop的分析流水线。 HDInsight上的Storm非常容易准备,我们可以通过Auzre门户或者PowerShell来创建,只需要选取简单的几个参数,等上15分钟就可以创建完毕。如果我们使用Visual Studio, 那么HDInsight Tools for Visual Studio可以用来创建C#或者混合C#/Java的topology,然后提交到HDInsight集群中的Storm。HDInsight Tools for Visual Studio还提供了接口来监视和管理Storm topology。 每个HDInsight集群里的Storm都提供了一个基于Web的仪表盘,我们可以用它来提交,监视,和管理所运行的Storm topology。Storm还通过Event Hub Spout提供了跟Azure Event Hub集成的功能。 Storm可以保证每个收入的消息都被完全处理,即使数据的分析分散到几百个节点。 Storm是跑在Hadoop的Yarn框架上的应用。同Hadoop的Job Tracker类似,Nimbus 节点提供了类似的功能,它可以通过Zookeeper来把任务分配到其他节点。Zookeeper节点提供集群的协调并且提供Nimbus和工作节点上的Supervisor进程的通讯。如果有处理节点坏掉了,Nimbus会被通知到,然后它会把任务和相关数据交给其他节点来处理。缺省设置下Apache Storm有单个Nimbus节点,而HDInsight里的Storm有2个Nimbus节点。如果主节点失败了,集群会切换到从属节点。现在HDInsight支持动态调整工作节点的数量,就算是在处理数据的时候也可以进行动态调整。   那么Storm是如何来处理数据的呢? 我们知道Hadoop是运行MapReduce作业的,而Storm是运行topology的。Storm集群包括2类节点:运行Nimbus的主节点,和运行Supervisor的工作节点。 …

0

AzureML中的聚类

聚类是一种无监督的机器学习。在聚类中,目标是为了把相似的对象归组到一起。通常聚类算法可以分成以下几类: Partitioning分区:可以把数据集分成k个分区。每个分区对应于一个簇。 Hierarchical 分层:基于数据集,分层会通过自下而上或者自上而下地创建簇。在自下而上的方式中,算法开始把每个项分派给一个簇,在算法从层次网上移动的时候,它把个别相似的簇归并成更大的簇。这样一直继续下去知道所有的簇都归并到一个也就是层次的根节点。在自上而下的方式中,算法是把所有项目都归于一个簇,然后在每次反复的时候分成小的簇。 Density 密度:该算法考虑邻居的密度,也就是项目数量。通常它用来找出有任意形状的簇。相反,分区依赖于距离的度量,所以生成的簇有普通的形状,比如球状。 在基于分区的聚类算法中,我们需要度量点和向量的距离。通常的度量方式有欧式距离,余弦距离,曼哈顿距离等等,在Azure Machine Learning中,K-Means Clustering支持欧式距离和余弦距离的度量。对2个点p1, p2来说,欧式距离就是这两点连线的线段长度。欧式距离也可以度量两个向量的距离。而对2个向量v1和v2来说,余弦距离就是v1和v2的角度的余弦。 在聚类时使用的距离度量取决于聚类数据的类型。欧式距离对向量的度量量级是敏感的。比如说,尽管2个向量看上去很相似,特征的数量级会影响欧式距离的值。在这种情形下,用余弦距离可能更合适,因为余弦距离对数量级不敏感。2个向量之间的余弦角度是很小的。 Azure Machine Learning中使用K-Means算法来聚类。这个算法过程是这样的: 从数据集随机选取k个项目作为初始中心。 对剩下的其他项目,根据项目和簇中心的距离把他们分配给对应的簇。 重新计算每个簇新的中心。 重复步骤2和步骤3直到簇不再发生变化或者达到了最大的重复数。   如图所示,这是一个K-Means的聚类,这里分成了3个簇,用三种颜色来标明。    如图所示,这是对于k=3的时候算法计算的过程。+号是每次计算的簇的中心。一次次降低了平均误差平方根变得越来越准确反应簇的中心。 在K-Means Clustering模块中,它支持不同的簇中心的初始算法。这是通过Initialization属性来设置。我们可以用下面5种初始算法: Default缺省的,就是选取起始的N个点作为初始中心。 Random随机的,随机选取初始中心。 K-Means++ 采用K-Means++的中心初始化。 K-Means+Fast 在每次重复中选取最远的中心作为初始中心。 Evenly 选取均匀的N个点作为初始中心。

0

SQL Server连接问题案例解析(2)

在本篇博文中,我将为大家介绍一个在使用数据库镜像功能时发生的连接超时问题。关于数据库镜像的关键概念和术语,在之前的博客数据库镜像故障转移后,.NET应用程序连接SQL Server 超时(译文)中已经有了详细的介绍,这里不再赘述,大家可以参考前文。   问题描述 ======== 客户想要在Lync上安装一些组件,在后端需要从英国站点向澳大利亚站点的SQL Server建立连接时会如下错误: 英国站点: 服务器: lyncdben01 澳大利亚站点: 服务器: lyncdbau01 在问题调查的过程中,我们分别收集到了连接成功时与问题发生时的Netmon 日志。从dump 文件中,得到了Powershell命令背后真正的连接字符串: Data Source= lyncdbau01.group.local\lyncdbau01;Database=xds;Max Pool Size=4;Connection Reset =false;Enlist=false;IntegratedSecurity=true;Pooling=true;Failover Partner= lyncdbau02.group.local\lyncdbau02;   问题分析 ========= 1. 连接字符串很明确的告诉了我们,这是一个用于连接使用了镜像的数据库的连接字符串。如果一个连接尝试失败或者重试时间过期,则数据访问接口将尝试连接镜像的另一个伙伴。如果此时未打开连接,则数据访问接口还会尝试使用初始伙伴名称和故障转移伙伴名称,直到连接打开或登录期限超时。 重试时间为登录期限的某个百分比数。在后续的每轮中,连接尝试的重试时间会逐渐变大。在第一轮中,两次尝试的每次重试时间都是总登录时间的 8%。在后续的每轮中,重试算法会按相同的百分比增加最大重试时间。因此,前八次连接尝试的重试时间如下: 8%, 8%, 16%, 16%, 24%, 24%, 32%, 32% 重试时间使用以下公式进行计算: RetryTime = PreviousRetryTime + ( 0.08*LoginTimeout) 其中,PreviousRetryTime 初始值为 0。 例如,如果使用默认的登录超时期限 15 秒,则LoginTimeout = 15。在这种情况下,前三轮中分配的重试时间如下:…

0

SQL Server 连接问题案例解析(1)

      Microsoft Network Monitor(Netmon)是由微软发布的一款网络协议数据分析工具,利用Netmon可以捕获网络数据并进行查看和分析。在处理SQL Server 的连接问题时,Netmon常常会起到关键的作用。在本篇博文中,我将为大家分享一个通过使用 Netmon 解决的经典案例。       在这个案例中,客户发现在客户端的 SQL Server Management Studio 中执行某一个Query时就会发生错误,错误信息是“connection forcibly close by the remote server ”。 为了调查连接被关闭的原因,我们在客户端和服务端抓取了Netmon。       在正式分析这个案例前,我们先来介绍一些有关Netmon的知识和使用技巧。                                                      Netmon界面   1. 在Netmon界面中的Frame Summary 部分,我们首先可以看到Frame Number,不管我们在浏览时是否有设置filter,Frame Number的值是不会发生改变的,它相当于Frame的一个行号。 2. 在左侧Network Conversation 中,我们会看到进程的name和ID,在示例中即为Ssms.exe和3352。继续展开后看到IPv4,那么我们可以知道这个conversation是从哪里来的。再次展开可以看到这个conversation的端口,本示例中,端口为1433到49428 。在这里需要额外讲解一下,当客户端程序创建连接到SQL Server时会使用哪些端口呢?客户端会向操作系统申请并使用一个动态端口并向SQL Server发送连接请求。如果在连接时使用的是machine name,provider默认会去连接1433端口,这是一个provider的行为,改变这个行为需要修改注册表:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\<Provider>\tcp\DefaultPort 3. 当客户端尝试创建一个连接到SQL Server,有了source port和destination port后,在这两个port中就会形成一组physical tcp连接。形成连接之后每发一个包,包的sequence值就会发生变化。请注意,只有在同一个物理连接中,sequence的变化是连续的。如果客户端和SQL Server建立了两个不同的物理连接,这两个连接中的sequence没有任何关系。 4. Netmon中数据量很大,如果查看呢? 比如在浏览一个比较大的Netmon时,我们发现了一个Reset  Flag: 21  3:46:20…

2

Windows Azure SQL 数据库介绍系列 (4)

本文是Windows Azure SQL 数据库介绍系列最后一个博文,我们将给大家介绍的是SQL数据库的监控和审计功能。 监控 SQL数据库的性能和运行状态决定着您的业务负载是否可以流畅的读取和写入数据,并为用户提供良好的访问体验。因此作为一个管理员,您需要随时了解数据库的各项运行指标。Windows Azure为您提供了一种十分便利的监控与管理方式,您只需通过Windows Azure 管理门户,即可直观的通过图表看到这些信息。 通过Windows Azure管理门户-> SQL 数据库,在列表中选择所需的监控的数据库,点击“监视器 (Monitor)”,您可以了解该Windows Azure SQL数据库的概览信息,以及当前各项指标。除了默认显示的指标外,您还可以通过底下的菜单(Add Metrics)来添加更多监控指标,另外您也可以通过添加规定 (Add Rule)来制定警告阀值,一旦有警告将可以通过邮件通知发给管理员。 SQL数据库同时也提供了丰富的DMVs和系统表可以用来监控数据库的各项情况诸如连接和性能问题。例如如下DMVs和系统表就非常有用: sys.dm_exec_connections sys.dm_exec_sessions sys.dm_exec_query_stats sys.dm_tran_locks sys.dm_db_resource_stats sys.resource_stats sys.bandwidth_usage sys.event_log sys.database_connection_stats sys.database_usage 更多信息: Azure SQL Database Performance Guidance https://msdn.microsoft.com/en-us/library/azure/dn369873.aspx Monitoring Azure SQL Database Using Dynamic Management Views https://msdn.microsoft.com/library/azure/ff394114.aspx?amp;clcid=0x804 审计 审计功能能帮助您维护公司的安全条例,了解数据库中的活动和及时发现安全问题。 可以审计的事件有如下: 数据访问 Schema更改(DDL) 数据更改(DML) 帐户, 角色和权限(DCL) 安全例外…

0