标签 sql server 下的文章

SQL Server数据库中生成流水号

在有的业务场景中,需要生成连续的流水号,如果不考虑其他情况,比如数据库迁移、最后一条被删除等,可以直接使用自增字段;如果要考虑以后的数据迁移,可以用这种方法:
首先有一张表,记录最大ID,每次生成流水号的时候,都是先从这张表中取出最大ID,之后+1更新:
如:

declare @serial_number int;
set @serial_number=0;
update tb1 set @serial_number=maxid,maxid=maxid+1;

这里使用update,利用的是SQL Server的更新锁,就不需要自建锁影响性能。

如果后续使用该流水号时失败,就需要整个回滚,再加个事务:

    declare @serial_number int,@status int;
    set @serial_number=0;
    set @status=0;
    begin tran tran_serial:
    begin try
        update tb1 set @serial_number=maxid,maxid=maxid+1;
        --其他业务逻辑
        insert into t_serial (serial_number) values (@serial_number);
        set @status=1;
    end try
    begin catch    --出现异常,回滚事务
        rollback tran tran_serial;
    end catch
    
    if(@status=1)
    begin    --如果执行成功没有发生异常,提交整个事务
        commit tran tran_serial;
    end

以上。

ASP.Net Core 2.0 在Linux下连接SQL Server数据库问题

在ASP.Net Core 2.0下,通过Dapper来使用SQL Server数据库,在Windows系统下完全正常,而部署到Linux服务器上会出现连不上数据库的情况,从日志里看,报下面的错误:

Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=23; handshake=365; [Login] initialization=0; authentication=2; [Post-Login] complete=28022;

连接超时。开始的时候怀疑是防火墙的原因,检查了一下防火墙正常,而且通过telnet命令检查数据库的1433端口是通的,看来问题是出在.net core上。
通过百度和Google搜索相关的关键字,找到了一篇帖子:《Timeout Connecting to SQL Server instance from Linux》,说的是只有SQL Server 2008 及之前的版本会有这问题,SQL Server 2012及之后修复了这个问题。
检查了下自己的SQL数据库版本,是SQL Server 2008 R2 版的,正在此列。开了腾讯云的SQL Server云数据库连接测试,完全正常。

以上。

SQL Server on Linux 初体验

最近才无意中发现,原来微软已经在去年(2016年)的3月8日(一个特殊的日子)发布了SQL Server on Linux 版本,已经过去一年多了,我竟然才知道,真是大意了。无论怎样,还是先大概的试一下吧,至少先把数据库安装上看看长什么样子的吧。
先说下安装的前提条件:

1.CentOS(没提版本,最好是7.0以上吧,不确定6.x及以下的可不可以)、ubuntu 16.04或ubuntu 16.10,或docker等
2.要求内存最低3.25G
3.如果没有突发内存(即swap分区),不能安装,如部分不带swap分区的VPS就不能安装SQL Server

整体来说要求很高,下面开始安装体验。
之前看过其他的文章,说需要openssl 1.0.2以上版本,实际测试过程中发现并不需要;另外安装过程大概需要几分钟至几十分钟时间不等,取决于你的网络状态,下载服务器位于国外,会比较慢,最好是在screen下安装。我是在CentOS 7.1环境下安装的,下面简单说下安装过程:
1.添加centos的SQL Server源的配置文件:

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

2.使用yum安装SQL Server:

yum install -y mssql-server

3.配置数据库:

/opt/mssql/bin/mssql-conf setup

之后设置数据库sa账号的密码,要满足一定的复杂度,一般大小写英文+数字+特殊符号,长度8位以上,都可以满足。
4.查看数据库状态:

systemctl status mssql-server

5.设置防火墙,把1433端口添加到例外:

firewall-cmd --zone=public --add-port=1433/tcp --permanent
firewall-cmd --reload

6.更新:

yum update mssql-server

7.卸载:

yum remove mssql-server

卸载之后删除数据库文件:

rm -rf /var/opt/mssql/

到这里数据库部分就安装完了,下面可以安装可选包:

一、安装SQL Server工具包:
1.安装
如果工具包是在其他Linux服务器上安装,需要先添加源:

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

之后安装:

yum install mssql-tools unixODBC-devel

2.更新:

yum check-update
yum update mssql-tools

二、安装全文检索:
1.安装:

yum install -y mssql-server-fts

2.更新:

yum check-update
yum update mssql-server-fts

以上过程完毕后,我们的数据库就彻底安装完了,下面是使用。
Linux下:
1.连接数据库:
需要安装SQL命令行工具,具体包含在前面安装的SQL可选工具包中,如果你已经安装了,那么使用下面的命令连接:

sqlcmd -S localhost -U SA -P '你的SA账号的密码'

2.查询可用的数据库:

SELECT Name from sys.Databases;
GO

3.创建数据库:

CREATE DATABASE testdb;
GO

4.选择指定的数据库:

USE testdb;
GO

5.建表:

CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
GO

6.向表中插入数据:

INSERT INTO inventory VALUES (1, 'banana', 150);
INSERT INTO inventory VALUES (2, 'orange', 154);
GO

7.查询:

SELECT * FROM inventory WHERE quantity > 152;
GO

8.退出SQL命令行:

QUIT

Windows下连接:
可以使用SQL Server Management Studio (SSMS)、PowerShell和SQL Server Data Tools (SSDT)。
使用SQL Server Management Studio的话,需要安装新版本:https://go.microsoft.com/fwlink/?linkid=840957,之后使用方法与Windows下的一致。

以上。

几段排查SQL Server占用CPU过高的SQL

1.查看当前的数据库用户连接有多少

 USE master
 GO
 --如果要指定数据库就把注释去掉
 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
 SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

2.查看各项指标是否正常,是否有阻塞,选取了前10个最耗CPU时间的会话

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'  
ORDER BY [cpu_time] DESC

3.查看具体的SQL语句,需要在SSMS里选择以文本格式显示结果

--在SSMS里选择以文本格式显示结果
SELECT TOP 10 
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
ORDER BY [cpu_time] DESC

4.查看CPU数和user scheduler数和最大工作线程数,检查worker是否用完也可以排查CPU占用情况

 --查看CPU数和user scheduler数目
 SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
 --查看最大工作线程数
 SELECT max_workers_count FROM sys.dm_os_sys_info

5.查看worker是否用完,如果达到最大线程数的时候需要检查blocking

SELECT
scheduler_address,
scheduler_id,
cpu_id,
status,
current_tasks_count,
current_workers_count,active_workers_count
FROM sys.dm_os_schedulers

对照表:
各种CPU和SQLSERVER版本组合自动配置的最大工作线程数

CPU数32位计算机64位计算机
<=4256512
8288576
16352704
32480960

6.查看会话中有多少个worker在等待

 SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句', 
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

7.查看ASYNC_NETWORK_IO等待

(注:比如我当前执行了查询SalesOrderDetail_test表100次,由于表数据非常多,所以SSMS需要把SQLSERVER执行的结果慢慢的取走,造成了ASYNC_NETWORK_IO等待)

 USE [AdventureWorks]
 GO
 SELECT * FROM dbo.[SalesOrderDetail_test]
 GO 100

8.查询CPU占用高的语句

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

9.查询缺失索引

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

分类

最新文章

最近回复

  • 老徐: 已经加上了,抱歉才看到
  • 青山: 某种原因,暂停友链,抱歉。
  • 搬瓦工: 朋友 交换链接吗
  • 飞刀说: 名称:飞刀说 描述:...
  • 青山: 计划搬迁到腾讯云,正...
  • 河边的飞刀: 网站名称:飞刀说 网...
  • 老徐: 具体要哪个呢?
  • 老徐: 是不是有点老?
  • 青山: 哇,林志炫
  • 老白: 哇,这改的可以,能不...

归档

标签云

C# .net core asp.net 情感 SQL mongodb sql server EasyUI 安全 激活 linux 身份验证 https typecho .net sql注入 kms MVC IIS 高并发 IE 坑爹 服务器 mysql Oracle Combobox Datagrid 口语 数据抓取

其它