2016年11月

几段排查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;

.Net Core系列教程(三)——使用Mongodb

本文中,假定你已经安装好了Mongodb、及ASP.NET Core的开发环境。本文主要说下在ASP.NET Core中怎样连接Mongodb数据库:
一、在ASP.NET Core中使用Mongodb,首先要安装C#的Mongodb Driver,现在最新的是2.3版本,使用Nuget直接安装就可以。
二、配置数据库的连接,打开appsettings.json文件,添加下面设置:

  "ConnectionStrings": {
    "MongodbConnection": "mongodb://127.0.0.1:27017"
  }

三、新建一个关于连接数据库的Model类,我是放在Models类库中,具体可以根据自己的项目来

    public class ConnectionStrings
    {
        public string MongodbConnection { get; set; }
    }

四、打开Startup.cs文件,修改 ConfigureServices 方法,如下:

        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddApplicationInsightsTelemetry(Configuration);
            services.AddOptions();    //增加
            services.Configure<IB.Models.ConnectionStrings>(Configuration.GetSection("ConnectionStrings"));    //增加数据库连接
            services.AddMvc();
        }

五、使用

        private readonly IOptions<Models.ConnectionStrings> _settings;    //取数据库连接串
        public DemoController(IOptions<Models.ConnectionStrings> settings)
        {
            this._settings = settings;
        }

以上

.Net Core系列教程(二)——细数.Net Core 1.1的坑

因为.net core我也是边学边用,会时不时的遇到些比较坑人的地方,会不定期的在这里更新,同时给出脱坑方法。目前有如下这些坑:

1.如果开发环境没有安装.Net Core 1.1,而服务器上是.Net Core 1.1的环境,那么。。。你的程序在服务器上跑不起来,向下不兼容,要问怎么解决,打开你的工程,打开nuget,更新,把里面的全都更新了,之后再重新编译部署就可以了。

2.按照官网文档https://docs.microsoft.com/en-us/aspnet/core/publishing/linuxproduction进行部署,到使用systemd来管理.net core站点启动这一步的时候,无论你怎么操作都是不会像官方那样成功的,而是会报500错误,而直接使用dotnet命令来运行的时候又没问题,使用journalctl 命令来看日志,会看到这些:

    root@biz126:/home/wwwroot/netcore/domain/tool.dwz.nz/web# journalctl -fu kestrel-dwztool.service
    -- Logs begin at Fri 2016-11-18 12:10:41 CST. --
    Nov 19 22:19:04 biz126 dotnet-tooldwznz[14275]: Application started. Press Ctrl+C to shut down.
    Nov 19 22:51:46 biz126 systemd[1]: Stopping tool.dwz.nz website...
    Nov 19 22:51:46 biz126 dotnet-tooldwznz[14275]: Application is shutting down...
    Nov 19 22:51:47 biz126 systemd[1]: Stopped tool.dwz.nz website.
    Nov 19 22:53:05 biz126 systemd[1]: Starting tool.dwz.nz website...
    Nov 19 22:53:05 biz126 systemd[1]: Started tool.dwz.nz website.
    Nov 19 22:53:06 biz126 dotnet-tooldwznz[17468]: Hosting environment: Production
    Nov 19 22:53:06 biz126 dotnet-tooldwznz[17468]: Content root path: /
    Nov 19 22:53:06 biz126 dotnet-tooldwznz[17468]: Now listening on: http://localhost:5000
    Nov 19 22:53:06 biz126 dotnet-tooldwznz[17468]: Application started. Press Ctrl+C to shut down.

从日志中的“Content root path: /”可以看到,把/文件夹作为了站点的根目录了,解决方法:打开Program.cs文件,添加using System.Reflection;,之后注释掉原来的.UseContentRoot(Directory.GetCurrentDirectory()),添加新的.UseContentRoot(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location)),再重新编译发布部署,就可以了

如果要在VS中调试的话,还要再改回去。

3.如果你所有的步骤都是按照官方文档进行,安装的时候:

    sudo apt-get install curl libunwind8 gettext
    curl -sSL -o dotnet.tar.gz https://go.microsoft.com/fwlink/?LinkID=835021
    sudo mkdir -p /opt/dotnet && sudo tar zxf dotnet.tar.gz -C /opt/dotnet
    sudo ln -s /opt/dotnet/dotnet /usr/local/bin

设置守护进程:

        [Unit]
        Description=Example .NET Web API Application running on Ubuntu

        [Service]
        ExecStart=/usr/bin/dotnet /var/aspnetcore/hellomvc/hellomvc.dll
        Restart=always
        RestartSec=10                                          # Restart service after 10 seconds if dotnet service crashes
        SyslogIdentifier=dotnet-example
        User=www-data
        Environment=ASPNETCORE_ENVIRONMENT=Production 

        [Install]
        WantedBy=multi-user.target

.Net Core 2.0.7 使用的systemctl配置如下:

[Unit]
Description=Example .NET Web API App running on Ubuntu

[Service]
WorkingDirectory=/var/aspnetcore/hellomvc
ExecStart=/usr/bin/dotnet /var/aspnetcore/hellomvc/hellomvc.dll
Restart=always
RestartSec=10  # Restart service after 10 seconds if dotnet service crashes
SyslogIdentifier=dotnet-example
User=www-data
Environment=ASPNETCORE_ENVIRONMENT=Production
Environment=DOTNET_PRINT_TELEMETRY_MESSAGE=false

[Install]
WantedBy=multi-user.target

启用服务:

    systemctl enable kestrel-hellomvc.service

启动服务:

        systemctl start kestrel-hellomvc.service
        systemctl status kestrel-hellomvc.service

你会发现,真是日了卵,http://localhost:5000无法访问,查日志:

    journalctl -fu kestrel-hellomvc.service

泥煤的,果然是有问题,站点没跑起来,什么原因呢?

问题就在这里(此处应有柯南):

安装的时候,dotnet的环境变量添加在/usr/local/bin里,而守护进程的服务设置的却是/usr/bin/dotnet,能好用才见鬼,把守护进程中的/usr/bin/dotnet换成/usr/local/bin/dotnet就好了,我也在本系列中的第一篇文章《.Net Core系列教程(一)——环境搭建》中提到过。

4.创建Web项目,之后运行,咦?不按套路出牌,默认的项目竟然直接报一大堆错,像什么

Can not find runtime target for framework '.NETCoreApp,Version=v1.0' compatible with one of the target runtimes: 'win10-x64, win81-x64, win8-x64, win7-x64'

1.The project has not been restored or restore failed - run dotnet restore

2.The project does not list one of 'win10-x64, win81-x64, win8-x64, win7-x64' in the 'runtimes' section.

3.You may be trying to publish a library, which is not supported. Use dotnet pack to distribute libraries.

而报错文件全都指向.net core的C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\DotNet\Microsoft.DotNet.Common.Targets文件的第262行,???一脸黑人问号。

报的错如下图:

我可是什么也没干啊,而且这报的还不是哪个代码的错误,这是哪里出了问题呢?这个也是从1.0升级为1.1之后出现的问题,解决办法(敲黑板,划重点):

打开项目的配置文件project.json,找到 <span style="background-color: #D8D8D8;"> "Microsoft.NETCore.App": "1.1.0</span>",,修改为:

    "Microsoft.NETCore.App": {
        "version": "1.1.0",
        "type": "platform"
    },

保存,运行,通过,哦了,搞定。

Debian 8安装Mongodb

1.添加apt源

$ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv EA312927

Ubuntu:

    $ echo "deb http://repo.mongodb.org/apt/ubuntu "$(lsb_release -sc)"/mongodb-org/3.2 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb.list

- 阅读剩余部分 -

分类

最新文章

最近回复

  • 青山: 某种原因,暂停友链,抱歉。
  • 青山: 计划搬迁到腾讯云,正...
  • 老徐: 具体要哪个呢?
  • 老徐: 是不是有点老?
  • 青山: 哇,林志炫
  • 老白: 哇,这改的可以,能不...
  • 老徐: 23333
  • 许建华: 我是为了表情包来的~
  • vultr vps: 感谢分享深入学习
  • 青山: 每一次都是不同的感受

归档

标签云

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

其它