보미네

http://msdn.microsoft.com/en-us/library/windows/hardware/jj123528.aspx

위 테스트는 서버에 processor groupsize 속성을 변경해 numa 를 강제로 생성하게 하는 방법이다.

여기에서 2개의 logical processor 가 하나의 group 으로 설정하고 싶다면 bcdedit.exe /set groupsize 2 이렇게 설정하면 된다.

예제는 다음과 같다

Consider a non-NUMA system that has one processor package (socket) that contains four processor cores and has Hyper Threading enabled, for a total of eight logical processors. If you run the following command to set the processor group size to two and then restart the system, the system will be configured to have four processor groups where each group has two logical processors, one core, and one NUMA node. GetLogicalProcessorInformationEx reports 8 logical processors, four processor packages, 4 nodes, and 4 groups. Each group contains one node, one package, and 2 logical processors.

bcdedit.exe /set groupsize 2

위 예제는 로지컬 프로세서가 8개 있을때 4개 프로세서 그룹이 생기고, 각 그룹당 2개의 로지칼 프로세서, 1개의 코어, 1개의 numa 가 생긴다는 설명이다. 

아래 그림은 계층도 이다. 참고하면 좋을 것이다. 나는 이것을 보고 2개의 누마를 생성하기 위해 프로세서 그룹 최대치를 위와같이 조정한 것이다. 이런 방법은 멀티 numa 환경에서 프로그램 특성을 테스트 하기위한 것이지 이게 실제로 numa가 생성된다는 말도안되는 말씀 하시면 안됩니다. ....

지우고 싶다면,

bcdedit.exe /set groupaware off

bcdedit.exe /deletevalue groupsize

명령을 수행하고 restart 한다.

SQL Server 2012 sp2 numa 환경에서 한쪽 numa 노드만 cpu 가 100% 임....

관련 cu 는 나와 있고 유사 환경을 꾸며 repro 할려고 했는데 안되네....

http://support.microsoft.com/kb/2928300/en-us

-_-~ 랄라라~ sql 을 -P 옵션으로 프로세서가 많은것처럼 꾸미면(병렬쿼리 비용계산을 시뮬레이션 할 때 주로 사용) sql 에서는 numa 인식을 또 못하네....그래서 데탑에서 repro 가 안되는것으로 판명

또! 아래 SQL QueryStress 이 프로그램 리소스를 은근 많이 사용한다. -_- 그래도 간단한 파라미터 쿼리나 부하 테스트 용으론 편하다. 쩝......ostress 쓰면 adhoc 호출에 의해 compile이 계속 올라가고.....워크로드 제너레이터를 간단히 만드는게 좋다.

바탕화면은 왜 이렇게 붉은지..........ㅎㅎㅎ 내 정신세계인가?

select
   name
 , case when status&1 = 1 then 'autoclose(ALTER DATABASE)' else null end
 , case when status&4 = 4 then 'select into/bulkcopy(SET RECOVERY를 사용한 ALTER DATABASE)' else null end
 , case when status&8 = 8 then 'trunc. log on chkpt(SET RECOVERY를 사용한 ALTER DATABASE)' else null end
 , case when status&16 = 16 then 'torn page detection(ALTER DATABASE) ' else null end
 , case when status&32 = 32 then 'loading  ' else null end
 , case when status&64 = 64 then 'pre recovery' else null end
 , case when status&128 = 128 then 'recovering ' else null end
 , case when status&256 = 256 then 'not recovered ' else null end
 , case when status&512 = 512 then 'offline(ALTER DATABASE)' else null end
 , case when status&1024 = 1024 then 'read only(ALTER DATABASE)' else null end
 , case when status&2048 = 2048 then 'dbo use only(SET RESTRICTED_USER를 사용한 ALTER DATABASE)' else null end
 , case when status&4096 = 4096 then 'single user(ALTER DATABASE)' else null end
 , case when status&32768 = 32768 then 'emergency mode ' else null end
 , case when status&65536 = 65536 then 'CHECKSUM(ALTER DATABASE)' else null end
 , case when status&4194304 = 4194304 then 'autoshrink(ALTER DATABASE)' else null end
 , case when status&1073741824 = 1073741824 then 'cleanly shutdown' else null end

 , case when status2&16384 = 16384 then 'ANSI null default(ALTER DATABASE)' else null end
 , case when status2&65536 = 65536 then 'concat null yields null(ALTER DATABASE)' else null end
 , case when status2&131072 = 131072 then 'recursive triggers(ALTER DATABASE)' else null end
 , case when status2&1048576 = 1048576 then 'default to local cursor(ALTER DATABASE)' else null end
 , case when status2&8388608 = 8388608 then 'quoted identifier(ALTER DATABASE)' else null end
 , case when status2&33554432 = 33554432 then 'cursor close on commit(ALTER DATABASE)' else null end
 , case when status2&67108864 = 67108864 then 'ANSI nulls(ALTER DATABASE)' else null end
 , case when status2&268435456 = 268435456 then 'ANSI warnings(ALTER DATABASE)' else null end
 , case when status2&536870912 = 536870912 then 'full text enabled(sp_fulltext_database로 설정)' else null end
from master.dbo.sysdatabases

--ALTER DATABASE BAMMA
--SET PAGE_VERIFY CHECKSUM;
--GO

--ALTER DATABASE joymdb
--SET auto_shrink off;
--GO

--exec sp_fulltext_database 'disable'

dbcc sqlperf(logspace)

-- vlf 갯수

DECLARE @query varchar(1000),
 @dbname varchar(1000),
 @count int

SET NOCOUNT ON

DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM master.dbo.sysdatabases

--drop table ##loginfo
CREATE TABLE ##loginfo
(
 dbname varchar(100),
 num_of_rows int)

OPEN csr

FETCH NEXT FROM csr INTO @dbname

WHILE (@@fetch_status <> -1)
BEGIN

CREATE TABLE #log_info
(
recoveryUnitId int,
 fileid tinyint,
 file_size bigint,
 start_offset bigint,
 FSeqNo int,
[status] tinyint,
 parity tinyint,
 create_lsn numeric(25,0)
)

SET @query = 'DBCC loginfo (' + '''' + @dbname + ''') '

INSERT INTO #log_info
EXEC (@query)

SET @count = @@rowcount

DROP TABLE #log_info

INSERT ##loginfo
VALUES(@dbname, @count)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT dbname,
 num_of_rows
FROM ##loginfo
WHERE num_of_rows >= 50 --My rule of thumb is 50 VLFs. Your mileage may vary.
ORDER BY dbname

DROP TABLE ##loginfo

-- 마지막 full backup
SELECT top 1 s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'joymdb'
and
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END ='full'
ORDER BY database_name, backup_finish_date desc

-- 마지막 로그백업
SELECT top 1 s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'joymdb'
and
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END ='Transaction Log'
ORDER BY database_name, backup_finish_date desc

-- 계정 없을때

net stop mssql$mynewins5
net start mssql$mynewins5 /m /f /T3608 /T7806
sqlcmd -S.\mynewins5 -A
USE [master]
GO
CREATE LOGIN [a] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [a]
GO
exit
net stop mssql$mynewins5
net start mssql$mynewins5

초단위로 수정할 수 있고

에러가 있을때 계속 진행할지 말지 정할수 있음

transaction handling code 틀린거 수정함

http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

service broker 만세~

1편은 simple한 버전이고, 1편을 적절하게 수정했음  

scheduler.sql

2편은 복잡하게 스케줄링 할 수 있음 예를들면 요일별로

http://www.sqlserverutilities.com/SQL-Server-Utilities-Free-SQL-Server-Tools-Software-and-Utilities-for-the-DBA-and-Developer.htm

Performance

RML Utilities for SQL Server (x86) and (x64) - tools to help database administrators manage the performance of Microsoft SQL Server: Overview

SQLQueryStress - automatically collects metrics to help you determine whether your queries will perform under load, and what kind of resource strain they put on your server

IO

Iometer - A great tool that measures and characterizes IO subsystems

SQLIO GUI - user interface for SQLIO Disk Subsystem Benchmark Tool

SQLIO Disk Subsystem Benchmark Tool - used to determine the I/O capacity of a given configuration

SQLTrace, Profiler & Extended Events

ClearTrace - ClearTrace imports SQL Server 2000, SQL Server 2005 and SQL Server 2008 trace and profiler files into SQL Server and displays summary performance information. It is designed to provide the same performance summary as Read80Trace but in a graphical environment and for SQL Server 2005 and SQL Server 2008

Trace Analyzer for SQL Server - DBSophic's Trace Analyzer helps you analyze multiple trace files and trace tables quickly and accurately. Within a few minutes all the trace events are analyzed, deparameterized, grouped, and clearly arranged in a tree format, graphically displaying their total and relative resource consumption. You can even drill down to specific parameter value sets and clearly pinpoint the batches or even specific parameter value sets that consume the most resources and slow down your database

SQL Server 2008 Extended Events Manager - This tool lets you create, delete, modify, start, and stop Extended Events sessions and metadata files in SQL Server 2008

Locking & Concurrency

BlocksTracer - BlocksTracer is a utility that monitors and captures SQL Server's blocking locks that occur on Microsoft SQL Server 2005 and Microsoft SQL Server 2008. You can configure a threshold in seconds that when exceeded by a Blocking Lock you receive a real time email notification and in addition the blocking information is saved to a log file and a database table which allows further analysis

MSSQL Blocks - This tool collects information from multiple SQL Server 2005 and 2000 instances about locked or blocked processes and stores this information in XML files for later analysis

Schema Scripting

SQLScripter - A utility that automates the process of schema generation allowing you to keep database schema history at points in time. Script Tables, Stored Procedures, Indexes, Constraints, Triggers, Permissions, Jobs, Logins and any other object

DbDiff - Compare SQL Server database structures and generate scripts

ScriptDb - data export and scripting utility for Microsoft SQL Server

Data Scripting

sp_generate_inserts - A great Tool. This stored procedure quickly generates INSERT statements to the data in your table

SQL Sripter - data export and scripting utility for Microsoft SQL Server

Backup and Recovery

SQL Safe - A great Tool that lets you create compressed backups

SQL Server Management Studio Add-in's

SSMS Tools Pack - an add-in with lots of IDE options (Query Execution history, regions, debug sections, CRUD stored procedures creation, new query templates, running custom scripts from Object explorer's context menu) for SQL Server Management Studio and SQL Server Management Studio Express

Create Database Snapshot - simple GUI for creating a database snapshot

Sort SQL Project Files - sorts SQL project files

The Black Art of Writing a SQL Server Management Studio 2005 Add-In - a how-to article and a Search DB Objects add-in with code

How to create SSMS Add-in - an article that describes how to create add-ins without any source code

Data Scripter - generates insert statements for a table

SQL Injection tools

Top 15 free SQL Injection Scanners - 15 free SQL injection scanners :)

Microsoft Source Code Analyzer for SQL Injection - a static code analysis tool for finding SQL Injection vulnerabilities in ASP code

UrlScan v3.0 Beta - a Microsoft security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process

HP Scrawlr - crawls a website, simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities

Blind Sql Injection Brute Forcer version 2 - accepts custom SQL queries as a command line parameter and it works for both integer and string based injections.

Administration

SQL Web Data Administrator - enables you to easily manage your SQL Server data from a web browser

SQL Server Cache Manager - allows you to return information about what is stored is the SQL Server cache and how efficiently it is being used.

SQL Internals Viewer - a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organized and stored.

Microsoft SQL Server 2000/2005 Management Pack - monitors the availability and performance of SQL Server 2000 and 2005 and can issue alerts for configuration problems.

Performance Analysis of Logs (PAL) Tool - tool that reads in a performance monitor counter log (any known format) and analyzes it using complex, but known thresholds (provided).

Microsoft Baseline Security Analyzer - helps small- and medium-sized businesses determine their security state in accordance with Microsoft security recommendations and offers specific remediation guidance

SQLDumper.exe - comes with SQL Server. Installed at ..\Microsoft SQL Server\90\Shared\SQLDumper.exe. Generates a dump file on demand for any Microsoft Windows application.

SQL Server 2005 Partition Management - helps in data loading and extraction from partitions

SQLdiag Utility - a general purpose diagnostics collection utility that can be run as a console application or as a service (comes with SQL Server)

SQL Nexus - analyzes SQLDIAG captures

PSSDiag Utility - a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files

SQL Recon - performs active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise

SQL Server 2005 Express Profiler - a free SQL Profiler for SQL Express

Log Parser 2.2 - provides query access to log files, XML files and CSV files, the Event Log, the Registry, the file system, and Active Directory

Microsoft SQL Server Management Studio Express - apparently a lot of people don't know this exists. Stripped down version of SSMS

SQL Server 2005 System Views Map - a pdf file with an entity relation diagram of all DMV's

SQL Server 2008 System Views Map - a pdf file with an entity relation diagram of all DMV's

SQL Server Web Data Administrator - admin your SQL Server over the web

SQL Server Express Utility - simple interaction with SQL Server

TableDiff.exe - utility that compares 2 tables for differences in data and schema for SQL Server 2000 and 2005

DMVStats - collects, analyzes and reports on SQL Server 2005 DMV performance data

YourSQLDba - You can use this T-SQL stored procedure to automate routine database maintenance tasks such as database and log backups and consistency checks

Best practices, analysis, health and performance

SQL Server FineBuild - provides one-click install and best-practice configuration of SQL Server 2005.

SQL Server 2005 Assessment Configuration Pack for Sarbanes-Oxley Act (SOX) - contains configuration items intended to help you establish and validate a desired configuration for your SQL 2005 servers in order to support your Sarbanes-Oxley compliance efforts

SQL Server 2005 Best Practices Analyzer - lets you verify the implementation of common Best Practices in SQL Server 2005

SQL Server 2000 Best Practices Analyzer - lets you verify the implementation of common Best Practices in SQL Server 2000

SQL Server Health and History Tool (SQLH2) - collects information from instances of SQL Server to determine how SQL Server is being used

SQL Server Health and History Tool (SQLH2) Performance Collector - optional tool to collect performance counters that are consumed by the SQLH2

SQL Server Health and History Tool (SQLH2) Reports - reports needed to view the data collected by the SQLH2

SQL Server 2005 Performance Dashboard Reports - performance reports that integrate into SQL Server Management Studio

Microsoft SQL Server 2000/2005 Management Pack - monitors the availability and performance of SQL Server 2000 and 2005 and can issue alerts for configuration problems

Database Publishing to hosted servers

Microsoft SQL Server Database Publishing Wizard - provides a way to publish databases to T-SQL scripts or directly to supporting hosting service providers

Microsoft SQL Server Database Publishing Services - deployed by shared hosting providers to enable publishing of SQL Server databases over the Internet

Upgrade and Migration

Microsoft SQL Server 2005 Upgrade Advisor - used when upgrading from lower SQL Server version to SQL Server 2005

Microsoft SQL Server 2008 Upgrade Advisor - used when upgrading from lower SQL Server version to SQL Server 2008

SQL Server Migration Assistant for Access

SQL Server Migration Assistant for Oracle

SQL Server Migration Assistant for Sybase ASE

SQL Server Analysis Services

MDX Script Performance Analyser - allows the user to identify how much each calculated member, named set and scoped assignment in an Analysis Services cube's MDX script contributes to the overall performance of a given MDX query.

Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 - SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007

SQL Server Integration Services

DTLoggedExec - allows you to run a Sql Server Integration Services (SSIS) Package producing a full and detailed logging information of execution status and package runtime data.

SQL Server 2005 BI Development Studio (BIDS)

BIDS Helper - Add-in for Visual Studio Business Intelligence Development Studio

Samples

SQL Server Analysis Services Product Samples

SQL Server 2005 Samples and Sample Databases

Non MS stuff

SQL CLR Project - a tool that allows you to deploy .NET assemblies to SQL Server. Pretty COOL if you work with SQL CLR a lot

SQL Designer - online SQL Database designer. Contains Importing and Exporting scripts to and from different formats

SQL Accord Community Edition - compares database schema

Bypsoft's DBTYP.NET - compares SQL Server, MySQL and Oracle databases. Cross database comparison of schemas and data.

SQL DB Tools - SqlDbDiff, SqlDbSearch and SqlDbSize utility

Idera's Free Tools - SQL Server backup and recovery tool, performance dashboard and logons and permissions transfer tool

SQL and other code Formatting

Jean-Claude Manoli's code formater - online formatting. I use this for my blog's code formatting. Has source code download and formats C#, VB, HTML/XML/ASPX , T-SQL and MSH

SQLinForm - online and desktop versions

Simple Talk's Prettifier - online format tool for T-SQL, C# and VB.

Free SQL Server books

"Boost your DBA expertise" and "DBA Best Practices" - two free books by RedGate

http://tempdb.tistory.com/83

http://www.sysprobs.com/how-to-show-my-computer-icon-on-windows-2012-server-desktop-add-run-to-windows-task-bar

역시 김치승!