Sogou T-SQL notes (1) - NOCOUNT ON SET to go and stay

By the T-SQL for a period of time after the preface, even if he has never used, is also more or less read SSMS set NOCOUNT on, a lot of performance optimization articles have mentioned this thing, they are suggested to use this command to reduce the pressure of network transmission, as far as possible, so today to see whether it is a chicken. ...
read(1510) comment(2)

Sogou 10 tips for Server SQL experts (translation and annotation)

When you point to this article, if you do not feel the need to read, but also hope that you can pull to the last look at the words! Source: https://technet.microsoft.com/en-us/magazine/gg299551.aspx translator's note: This paper mainly to translation mainly, but as work experience, translators have some of their own views and experience, and do not intend to completely copy the literal translation, filtering the some I think irrelevant information, if readers think look awkward, can see the original. This article may have been widely...
read(5544) comment(13)

Sogou Server SQL performance optimization - wait - SLEEP_BPROOL_FLUSH

Introduction: there is a for archiving of historical database (Historical Library), after the accumulation of a certain period of time, data file has reached more than 700 GB, later decided to some data may not be retained, put the data to truncate the and free up more than 600 GB of space, that is to say, after a contraction, theory on database only more than 100 g. Therefore, I passed all the reconstruction table (table number, but single table number or tens of millions) of the clustered index to shrink. But when the contract begins, the speed is extremely slow, even when the range of each contraction is reduced to 500MB...
read(1656) comment(2)

Sogou SQL Server (3) detailed execution plan operator (Compute Scalar) - calculated

Above: SQL Server (2) detailed execution plan operator series (Concatenation)...
read(1567) comment(1)

Sogou SQL Server (2) detailed execution plan operator series (Concatenation)

The above: SQL server execution plan operator Xiangjie (1) assertion -- (assert) objective: according to the plan. At the beginning of this paper, tells the story of another operator series (concatenation), the reader can according to this word (either in English or Chinese) to imagine why. In fact, it is quite intuitive, is to connect things, then we have to look at the bottom of what? How even? When is it even? ...
read(1363) comment(0)

Sogou SQL Server detailed implementation plan (1) - assertion operator (Assert)

A lot of places for the optimization of the statement, generally more reliable reply, even if the implementation of the plan to send out to see. Of course, those who just look at the statement on how to change the code, I have been rejected, because this is a pure. According to my experience, a large number of performance issues simply from the statement is difficult to find the bottleneck, the same statement, due to the different environment, the gap is very large, so it is more appropriate to analyze the implementation of the plan. So for the implementation of the plan, the general use of graphical execution plan is almost, but used to have some doubts, inside the icon (called the operator) is not very...
read(1857) comment(1)

Sogou T-SQL dynamic query (4) -- dynamic SQL

Above: dynamic T-SQL query (3): static SQL Objective: in front said a lot about the dynamic query. This paper introduces some methods for solving the dynamic query using dynamic SQL. Why use dynamic SQL: in many projects, dynamic SQL is widely used and abused, most of the time, dynamic SQL really is a solving method of choice for many needs. However, if it is not reasonable to use, it will lead to performance problems and can not be maintained. Dynamic SQL, in particular its own advantages and disadvantages, whether to use the need to evaluate the analysis: dynamic SQL advantages: dynamic SQL provides a powerful extension...
read(3959) comment(2)

Sogou T-SQL dynamic query (3) - static SQL

Above: dynamic T-SQL query (2) -- a keyword query this paper tells about static SQL knowledge and basic skills. Introduction: what is static SQL? Static SQL is relative to the dynamic SQL, in fact, we do not need to be too tangled precise definition, as long as you probably know what is static SQL can be. When a statement in particular is a stored procedure, the statement does not need to dynamically generated or stitching, in addition to the parameters we all know that the sentence final form, you can think this is a static SQL, in simple terms, most of our dynamic query statements belong to static sq...
read(1980) comment(0)

Sogou T-SQL dynamic query (2) -- Keyword query

Above: dynamic T-SQL query (1) -- a brief introduction to the objective: in the development process, we often will encounter a scenario similar to the following: applications have a query function, allowing the user to in query conditions to select the desired conditions. This is also the focus of this series. But sometimes you may find that some conditions are more or less mutually exclusive. For example, the user through below which a search condition information: 1. Customers name 2. Customers ID3. Customers identity identification number (such as the ID card, the U.S. social security number, etc.). And there are proper indexes on these three columns. This series of master...
read(1703) comment(1)

Sogou T-SQL dynamic query (1) -- Brief Introduction

Cause: due to the recent work needs and in the past has been the question, so decided to start studying the dynamic SQL. Due to leave first developed a year, a lot of skills no research, as the book, DBA and the SQL Server Performance Optimization and management of the art of the, more is attention to meet the functional requirements under the premise of performance problems. But I think this paper is not only useful to the DBA, the database developers and even designers, architects, and so there is a certain reference value. Objective: readers encountered a similar function: an application (whether B/S or...
read(4444) comment(1)

Sogou Server SQL index maintenance (1) -- how to get index usage

In the previous article, three kinds of common index problems have been mentioned. Then, how to deal with these problems when the system appears? In simple terms, analysis of the behavior of the existing system, then the process of index of: in case of insufficient index: check for missing index, also need to check existing index definition is a problem. For the case of too many indexes: analysis of the use of each index, to determine whether there is a need to exist or may be combined, may be modified. In the case of an index that is not reasonable, it is also an analysis of the definition of each index, and its usage...
read(2026) comment(0)

Sogou Server SQL index maintenance (1) -- the common index of the system

Objective: in many systems, such as I now management database, index is often abused, and even the use of DTA (Database Engine Tuning Advisor) to the bulk and create an index (DTA currently personally think it really useful should be found in the lack of statistical information, in the project, used a DTA, which suggests that the many columns lack of statistical information, later in without changing other operating under the premise, the statistical information manually built up to improve the performance of very obvious. This paper will open on statistical information). A table even has more than 20 indexes (the number of indexes is not the standard, but it should be as far as possible...
read(2852) comment(1)

Sogou APPLY usage in T-SQL (semi translation)

The above: T-SQL cross joins usage (semi) can also be used for the Microsoft Certified 70-461: Querying Microsoft SQL Server 2012 examination of learning. --------------------------------------------------------------------- following translation -----------------------------------...
read(1495) comment(0)

Sogou JOIN CROSS usage in T-SQL (semi translation)

Today to translate an article on the T-SQL, this article can be used for Microsoft certified SQL Server 70-461:QueryingMicrosoft 2012 of the study and practice of the use of. This article mainly focuses on the translation, and leads to some thoughts on personal work. I will try my best to translate this series (see the source of the original source of the relevant links, this is a series of articles) of the other articles, in addition to review some of the knowledge, the focus is to sum up their income. Am...
read(1468) comment(0)

Sogou Server SA SQL best practice (maybe not just the translation)

To be honest, the main part of this paper is translation, and due to the level of English, I did not fully translated, some of which I feel unimportant skip, should now appears to be from nine times out of ten, or will not affect the final effect. For English readers, to check on their own original. But this year I met a lot of things, but also a lot of things, I see a lot of information, but I remember the poor, to use more difficult to recall the time. I was lucky to know where to find it. ...
read(1688) comment(5)

Sogou Chapter 3 the Data Protecting (4): creating and using application roles

Database roles for managing access and permissions within the database. Members of a database role is by the client software such as SSMS to connect SQL Server database user, but you might want to grant a special user privileges, but only limited to a certain application and not SSMS, the first solution is using a dedicated SQL account to use as the application log. But the disadvantage of this program is the need to use Server SQL authentication, and can not identify which users are connected to Server SQL. But you can use the same application login name, and the use of Windows authentication, so you can better identify the user of the application, so as to enhance the rights when necessary. ...
read(2379) comment(0)

Sogou Chapter 3 the Data Protecting (3): creating and using database roles

Database level roles allow database permissions to manage as well as the server level roles. You can configure a fixed server role, create custom roles, etc.. ...
read(2214) comment(0)

Sogou Chapter 3 the Data Protecting (2): assign column level permissions

Server SQL permissions are hierarchical, a user has the schema level permissions, you have the right to all objects within the schema, unless you use the DENY permissions to remove the. But the object is not the lowest level in the hierarchy, you can set permissions to the column level. But the column level permissions override the permissions on the table by the GRANT. ...
read(2388) comment(0)

Sogou Chapter 3 the Data Protecting (1): understanding permissions

Relational database management system (RDBMS), such as Server Oracle, SQL, Mysql and so on, not only need to be responsible for access to data, but also responsible for ensuring the consistency and security of data. Is similar to other server systems, which controls the access to data by empowering a user and maintaining the user's session. Whenever you try to read and write data, Server SQL will check these permissions. ...
read(2427) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (11): correction of login mapping errors in a restored database

If you take the non contains a database from a server moved to another server, whether it is backup and restore or separating the additional, are likely to cause the SQL users to become orphaned users, which means they do not correspond to correlate to log. Because the login name and the mapping between users is based on SID, even if the new server has the same login name, but because of different SID, it will make the user can not be identified, thus forming an isolated user. If you migrate in the same field, then the mapping problem will only affect the SQL login, because the domain account (Windows authentication) SID in the active directory is the same. ...
read(2012) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (10): create a database containing

In Server SQL, security is divided into two levels: server level and database level. The login name of the server is mapped to a user in the database. Authentication occurs when the logon name is connected to the server. If the database has the corresponding user, then the login name will be able to access the database. The mapping between the login name and the user is based on an internal SID, when from a server replication database to another server, even if the user name is the same, if the SID is different, it will also intend this connection. From 2012 to introduce the concept of database Contained, which is used to solve this problem. ...
read(2023) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (9): to prevent the login name and user view metadata

Before Server SQL 2005, all servers and database metadata were visible to all. When the system based on online banking to share the Server SQL instance to the customer, it is possible to see other users of information. From the beginning of 2005, you can restrict the login name or user view unnecessary metadata by controlling the permissions. ...
read(2051) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (8): create a database user that maps to the login name.

Login name for authorization and access to server resources, if you need to access the database, the database needs to be mapped to the user. The user is the database level security subject, access to the database resources is granted to the user, not the login name. ...
read(3256) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (7): creating and using user defined server roles

From Server SQL 2012, no longer limited to the use of a fixed server role, you can create a custom role, allowing you to default special permissions. ...
read(1816) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (6): server permissions granted size

Before Server SQL 2005, only by adding the login to the fixed server role, in order to obtain management authority. Server SQL 2005 introduces a set of server size permissions that allow you to define a strict set of permissions to the server level. ...
read(1868) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (5): using a fixed server role

Login account allows you to connect to the Server SQL, and if there is a database user mapped to this account, then the account can also access the corresponding database. By default, they have no server level management rights. The fixed server role allows you to simplify authorization and recovery operations. ...
read(1935) comment(0)

Sogou Chapter 2 Authentication Authorization, User, Security and (4): limit the SA account management authority

SA account is SQL server system administrator, in the installation process, you can enable, prior to SQL Server 2005, this account cannot be modified, but from the beginning of 2005 can rename or disable the SA to reduce the risk of being attacked. In subsequent versions, Sa is only used as backward compatibility. Because SA is known to the public account, does not recommend the use of. ...
read(2339) comment(1)

Sogou Chapter 2 Authentication Authorization, User, Security and (3): protect the server to avoid violent attacks

Brute force attack (attack Brute-force) is a combination of almost all possible characters to try to the password, or use a dictionary table, containing almost all possible password to achieve a password ing method. If your password is simple, it will soon be broken. So, it is very important to test the password. ...
read(2079) comment(0)

Sogou Chapter 2 Authentication Authorization, and, Security User (2): to create a login account

Login account is defined in the server (instance) level, and can be granted to manage tasks, connections, access to databases, etc.. Server SQL security model has two levels: server level and database level. Login account must first be created at the server level. If you need to access the database, you need to create a user at the database level to map to the existing login name. ...
read(2683) comment(0)

Sogou Chapter 2 Authentication Authorization, User, Security, and (1): select Windows and SQL authentication

Server SQL has two kinds of authentication: one is the WIndows authentication, Windows authentication using the account on the Windows, and the use of its security token to verify. One is Server SQL authentication, using the Server SQL inside the definition of identity authentication. ...
read(2782) comment(0)

Sogou Chapter 1 Your Server Securing Network and (14): xp_cmdshell and OPENROWSET

Based on security reasons, certain features in the SQL server installation is disabled. From the beginning of 2008, all sensitive options can be through a call [peripheral configuration application is the aspect of management, the function in 2005 in the form of standalone tools appeared. In 2008 and cancelled. ...
read(2258) comment(0)

Sogou Chapter 1 Your Server and Network Securing (13): configuring endpoint security

Server SQL endpoint (Endpoint) is the gateway to access Server SQL, through the endpoint, anything can be transmitted between the network and Server SQL. Endpoint can be a system or user defined, in which the system endpoint allows the use of T-SQL to connect to Server SQL and send a query. ...
read(1997) comment(0)

Sogou Chapter 1 Your Server and Network Securing (12): protection of the link server

The link server (Server Linked) can make two different Server SQL instances and other types of RDBMS to exchange visits. Through the link server, you can achieve distributed query, similar to an application, the connection to the target server connection string stored up. ...
read(1920) comment(0)

Sogou Chapter 1 Your Server and Network Securing (11): the use of transparent database encryption

If you do not have access to the database file (MDF/LDF, etc.) to do permissions control, the attacker can copy the files to go, and then attach to their own machine analysis. The first layer of protection is the Server SQL file where the NTFS file system for permission control. If you want to further protect the database, you can use a transparent database encryption (Database Encryption Transparent, TDE), this function can protect the corresponding database of all files, no matter how many files. Because the file has been encrypted, even if these files are copied away, if there is no database master key, the same can not be used. At the same time, this encryption does not affect the user's use of the database, developers do not need to do additional work...
read(1925) comment(0)

Sogou Chapter 1 Your Server and Network Securing (10): use extended protection to avoid authorization relay attacks

In the process of exchange of visits between the client and the server, the authority is always maintained, through verification, can accept or reject the connection. ...
read(1708) comment(0)

Sogou Chapter 1 Your Server and Network Securing (9): using Kerberos for authentication

In the active directory (Directory Active), there are two authentication mechanisms: NTLM and Kerberos. Among them NTLM (LAN Manager NT) is based on the old version of the encryption method of the license agreement, Microsoft is not recommended to use. ...
read(1886) comment(0)

Sogou SQL Server Version List Microsoft (Server SQL version number)

What version of SQL server do I have? This unofficial build chart lists all of the known service packs (SP), cumulative updates (Cu), patches and hotfixes and builds of MS SQL Server 2014, 2012, 200.
read(12380) comment(0)

Sogou Chapter 1 Your Server and Network Securing (8): stop unused service

If you install Server SQL completely, there will be some non essential components will be installed up, these components will affect the performance, but also will bring greater security risks. ...
read(1662) comment(0)

Sogou Chapter 1 Your Server and Network Securing (7): disable Server Browse SQL

The SQL Server Browser service starts automatically when install SQL server cluster or a named instance. It is used to interact with the machine on the instance of SQL server, and let the client through the named instance is listening on the port for transmitting information. ...
read(1870) comment(0)

Sogou Chapter 1 Your Server Securing Network and (6): Server SQL access configuration firewall

Server SQL communication based on TCP and UDP ports, if you need to access the Server SQL from the machine on the machine, you need to open the port of the firewall. ...
read(2306) comment(0)

Sogou Chapter 1 Your Server and Network Securing (5): using SSL encryption session

If you want to protect these data by sniffer detection, need to use SSL encryption and interaction between client and server. ...
read(1650) comment(0)

Sogou Chapter 1 Your Server and Network Securing (4): the use of a virtual service account

Virtual service account (virtual service account) and hosting account, Windows Server 2008 R2 introduced new features, it is a do not need a local account password management, and can and networkservice account as access to the network through computer identification. ...
read(1743) comment(0)

Sogou Chapter 1 Your Server and Network Securing (3): the use of managed services account

Hosted service account (Servcie Account Windows) from Server Managed 2008 R2 appears, it is intended to be used to run the service's active directory account more easily managed. ...
read(2077) comment(0)

Sogou Chapter 1 Your Server and Network Securing (2): management services SIDs

Like SQL server which run in the security context of a Windows account service, if there are other services use the same Windows account to run, then these services (non SQL Server) will be likely to visit some unexpected resources...
read(1710) comment(0)

Sogou Chapter 1 Your Server Securing Network and (1): select Server SQL running account

Server SQL is a Windows service that runs on a Windows operating system with a Windows user or system user rights. It is very important to choose the right account to run Server SQL. This series of articles only focus on the security aspect. ...
read(2286) comment(1)

Sogou Server SQL Literacy Series - safety feature - Server SQL 2012 Cookbook Security

Server SQL Literacy Series - Security topics...
read(3494) comment(1)

Sogou Server SQL Literacy Series - mirror images

For the convenience of viewing, and in the form of thematic display, so I will put some articles...
read(5237) comment(0)

Sogou The development of NOT IN vs NOT EXISTS notes

IN NOT and EIXTS NOT have a certain risk when they are allowed to search for a column of null. Especially IN NOT, if the sub query contains a minimum of one NULL, will be the result of non - expected. Here is a demo. If object ID ('ShipmentItems',' U ') is a not null drop table dbo.ShipmentItems; go create table dbo.Sh...
read(2697) comment(4)

Sogou Third - second parts - fifth article configuration Server SQL mirror image - domain environment Server SQL mirror daily maintenance

Build image is not maintenance than do not build more dangerous. This paper introduces some basic knowledge about this aspect, to play a valuable role. ...
read(3613) comment(0)

Sogou The third part -- the second part -- the third article configuration Server SQL mirror image -- the domain environment

Source: This article will demonstrate how to in the environmental field deploy the image in the domain deployment relatively simple many, but many enterprises doesn't really need to use domain to manage server (where I is one), so it is necessary to demonstrate non domain environment, and focus in the domain environment. However, as a practical experience and the best advice, the strong use of the domain environment management. Non domain environment will be demonstrated in the fourth article: http://prog3.com/sbdm/blog/dba_huangzj/article/details/27652857. This paper only focus on the domain environment...
read(3754) comment(1)

Sogou The third part -- the second part -- the fourth article configuration Server SQL mirror image -- non domain environment

Front has been demonstrated to build domain of image, this paper will use non domain environment to build a mirror. Similarly, according to without the witness server of high security mode (synchronous) way to build, then demonstrates the asynchronous mode will eventually demo with witness server of high security mode. ...
read(6509) comment(8)

Sogou Third - second parts - second text plans to build Server SQL mirror

This paper follows the last chapter: SQL Server image profile of this source: as the saying goes: to do good work must first sharpen his tools. Planning how to deploy and use mirroring can reduce a lot of unnecessary risks. This article will be displayed in the form of three steps, but to pay attention to this is not the only standard, the specific circumstances of the specific analysis. The first step: to understand the environment in the construction of Server SQL mirror, you must first understand the environment you want to deploy, in order to determine the configuration of the mirror image. This is not only the premise of the mirror configuration, but also the deployment of Server SQL and even build a data platform and other high availability...
read(3382) comment(0)

Sogou The third part -- the second part -- the first article Server SQL mirror image

Mirror is what? It is a mirror (not used the mirror? You always have to pee without a mirror? A joke), meaning of mirror here are two: 1, the same below are introduced in detail, including libname, data files, and log files storage path should be. 2, see, but can not be used, the mirror Library in the absence of any processing is not accessible. ...
read(7449) comment(1)

Sogou The third part -- the sixth part -- the second article surveillance Server SQL mirror image

To optimize, first of all to monitor to see if there are performance issues, if there is, where to. To start a real optimization...
read(3367) comment(3)

Sogou Why database version control is necessary

Version control in the development process is very common, but in the world of database, version control is the two, and even three, and so on. When multiple people at the same time, the development of a database management and application system, using reasonable methods to monitor the database tables, stored procedures, and views of the object change is very important, each time you make a change to a database, need to do a detailed record, a little inattentive problems. I've worked for several companies in process development version control do very in place, but database is a blank, in the development process, often involving to the table increase column, and modify columns, modify stored procedures, and views...
read(4474) comment(3)

Sogou The first article -- the first article Server SQL backup Foundation

Before you read this article, please make a full database backup of all the important libraries you have. The following official start backup restore journey. Source: why the http://prog3.com/sbdm/blog/dba_huangzj/article/details/22683687 backup? The reason is very simple: to restore / resume. Of course, if you don't back up, you can recover the missing files from disk, but Server SQL is very angry, the consequences are very serious. When You'll see. why first call you back once again...
read(5118) comment(2)

Sogou Some misunderstandings in Server AlwaysOn SQL

AlwaysOn since SQL server2012 has released a long time the. Recently I to some customers do counseling often asked is not should use AlwaysOn, from the perspective of the customer as AlwaysOn is a cure all medicine, but in fact there is no cure good medicine. So here I talk about the common errors in AlwaysOn. 1.AlwaysOn can achieve load balancing. The answer is no, AlwaysOn can afford a read - only load in a given condition, which needs to be modified...
read(10666) comment(12)

Sogou (Draft) Server SQL replication (Replication) series (2) - transaction replication

This paper demonstrates how to build the most basic transaction replication. Environment ready: virtual machine 2: the server names were RepA and RepB, RepA for the release of the server, RepB for the subscription server. Are installed 2008R2 WindowsServer English Version (in foreign companies work reasons). And to ensure that two servers can exchange visits. Server SQL: in the above virtual machine were installed in the R2 2008 x64 SQLServer. Demo database: Microsoft sample database R AdventureWorks2008...
read(8064) comment(3)

Sogou SQLServer literacy

I would like to record my growth process, and to share with you the Server SQL database management system users. This series contains a personal view of the DBA should have the quality of a series of articles will be displayed in the list below, will continue to update, please pay attention and guidance, thank you. The following article has a priority, but that doesn't mean that is not fully reversible, according to the needs of the work may not in order to write, has written part can through the following hyperlink Click: first part: DBA first priority, backup and restore this article includes following subjects: SQL server backup based full backup differential backup...
read(3307) comment(11)

Sogou DBA daily management - Data Archive (Archiving-Data)

Source: http://www.sqlnotes.cn/post/2013/09/05/DBA-Daily-Jobs-One%E2%80%94%E2%80%94-Archiving-Data are my blog problem: with database is more and more big, the challenge of managing performance and will be more and more. Each query may need to look for more data pages, especially when the query has a scan operation, it will lead to more and more slow query. At the same time, the need to back up the data will be more and more, the backup operation...
read(4215) comment(0)

Sogou The tenth chapter -- maintenance index (9) -- the space of monitoring index consumption

Performance optimization is one of the work of DBA, but at the same time, DBA also need to care about the SQLServer instance, the database consumption space, providing the database user high availability, the management database backup strategy and so on. Disk space management is a very important thing, DBA need to pay attention to the disk space and unused index space consumption, the management of data files and the size of the log file, etc.. In other chapters and in this chapter, the index will consume disk space. So it's time to manage this part of the content. Preparations: before we begin, you must make sure that the user has: 1, VI...
read(2811) comment(0)

Sogou The tenth chapter - maintenance index (8) - creates an index in the column to improve performance.

Understand the basic knowledge of the computed column before you understand the index on the computed column. The computed column can be calculated by the expression that can be used by other columns in the same table. Expressions can be non computed column column names, constants, functions can also be any combination of the above mentioned elements are connected by an operator or operators. The expression cannot be a sub query. By default, the computed column is a virtual column, and can be re computed at the time of the call until it is used in the TABLE CREATE or TABLE ALTER command PERSISTED. If the column is defined as PERSISTED, the...
read(3139) comment(0)

Sogou The tenth chapter -- maintenance index (7) -- using indexed views to improve performance

View is a virtual table that contains one or more tables. Typically, it is only the object that stores the query, a view can be used as a table, it can be used to store procedures, JOIN, user defined functions, and so on. View contains the following two main characteristics: 1, provides a security mechanism, used to restrict user access only to the particular data. 2, logical view that developers can customize the user. When you view a query, the optimizer will produce a single execution plan for the query. The view must resolve the query during the execution of the query before the index view does not appear...
read(3317) comment(0)

Sogou The tenth chapter -- maintenance index (6) -- finding the useless index

As is known to all, the index has a great effect on the improvement of the performance. But this is a price, the index needs disk space to store its B-Tree, and used to update the information after each DML execution, so it should be checked in a certain period of time without useless index. Preparation: remember don't restart the server after the collection of information, because the DMO will be empty, and the data will be very inaccurate or even wrong. So it should be in the running for a period of time (business cycle) and then do the collection. Business cycle according to the actual situation. Some tables and queries will be used every day, but some of the similar statements...
read(3428) comment(1)

Sogou The tenth chapter -- maintenance index (5) -- finding missing index

In the development phase, it is difficult to always create a suitable index on the appropriate column. So the index that is created at the beginning may not be valid. Generally speaking, when a query is executed, the SQL Server optimizer will choose the most suitable index for execution when did not find a suitable index words, the optimizer will produce a suboptimal execution plan and the loss of the indexing information stored in the DMVs. When the SQLServer service is restarted, the information stored on the DMVs is lost, so it is best to collect these after 1 weeks of normal use...
read(3434) comment(0)

Sogou In the tenth chapter, maintenance index (4) improves the performance through the reorganization index

If the fragmentation level is less than 30%, it is recommended to use recombination instead of reconstruction. Because the reorganization will not lock the data pages or data tables, and reduce the CPU resource. Generally speaking, the reorganization will clear the current B-TREE, in particular, the index of leaf nodes, the reorganization of data pages and eliminate debris. And reconstruction is different, the reorganization will not add any new data pages. Preparatory work: in order to understand whether it is necessary to reorganize indexes, need to first check the degree of fragmentation, if below 10%, then there is no need to do maintenance, if from 10% to 30%, it is recommended to restructure. The following steps: 1, all kinds of heavy...
read(3220) comment(0)

Sogou In the tenth chapter, maintenance index (3) improves performance by rebuilding index

Reconstruction of an index is simply an internal deletion and reconstruction of the index, which makes the fragments disappear, the statistical information is updated, and the physical order is rearranged. It will compress the data page, fill in the appropriate data in accordance with the fill factor. If there is a need, you will add a new data page. These operations help improve the speed of data search, but this work if it occurs in the large table above, it will be very time consuming and resources. Preparation: first, to decide whether to rebuild the index of the critical value. Otherwise, the reorganization index will be better. When more than 30% pieces of debris, then rebuild the index will be better. There are two ways to rebuild the index...
read(3581) comment(0)

Sogou The tenth chapter - maintenance index (2) - filling factor

Objective: in Chapter 9, has introduced the how to use the index, when creating an index, B-tree format to store the data, with a root node, intermediate nodes and leaf nodes. Leaf node is the lowest level of the node, in the clustered index, contains the actual data, and each data page has 8KB. When the data in the table of the additions and deletions occur, will try to put the data into the appropriate data page. For example, there is a clustered index on SSN, when a new SSN number is inserted. SQLServer will try to insert the data into the appropriate data pages, assuming that SSN...
read(3379) comment(0)

Sogou The tenth chapter -- maintenance index (1) -- index fragment

This series includes: 1, find debris. The fill factor 2. 3, the use of REBUILD to indexing performance. 4, using REORGANIZE to add index performance. 5, how to find the missing index. 6, if the search index useless. 7, improve performance by creating an indexed view. 8, through the creation of index in the calculation of column increase performance. 9, index calculation of disk space consumption. Preface to the: DBA daily tasks and is not only create the need for an index in the corresponding column. As a matter of fact, DBA also maintains an index created high standards. Zhouerfu...
read(3073) comment(0)

Sogou The nineteenth chapter - the use of resource control device management resources (3) - monitor the control of resources

Objective: after each application configuration resource control, need to monitor resource control. May need to monitor the use of the pool of resources and how many requests are allocated to a specific resource pool. May also want to monitor the activities of the internal and default pools. In this paper, the demo using different login account (aw AW_WebAppUser and aw AW_ReportAppUser), and monitor the usage of CPU and memory resources. Preparation: the parts of the script has been written in the first article in this series. Not here...
read(2278) comment(0)

Sogou In the nineteenth chapter, using resource control resource (2) -- using T-SQL to configure resource controller

In the previous chapter, we have demonstrated how to use SSMS to configure resource control devices. But as DBA, there is always a need to write scripts, because it can reuse and extend. And you can quickly deploy in different servers. The following steps: to demonstrate how to achieve: 1, open SSMS, even to SQLServer. Ensure that the login account has CONTROLSERVER permissions. 2, run the following script object resource control delete created: USE master front GO WORKLOAD GROUP rg_We DROP...
read(2305) comment(0)

Sogou The nineteenth chapter - the use of resource control device management resources (1) - the use of Management Studio SQLServer configuration of resource control device

This series includes: 1, using SQL Server Management Studio resource allocation control device 2, using T-SQL resource allocation control device 3, resource monitoring control device Objective: in previous chapters, mentioned too can through a variety of configuration database server to improve performance. Such as index, statistical information, hints, physical design and server configuration, etc.. When you complete the configuration above, still have a small amount of stored procedure, query runs very slowly, due to the limited hardware resources, may have nothing to adjust...
read(3716) comment(1)

Sogou The eighteenth chapter -- policy based management (2) -- restricted database objects

In the actual environment, there will be a lot of developers are working on a project to write SQL script, this time need to define the object of the naming of the line. Completion of this task can be implemented using a number of coercive strategies. Reasonable naming objects are not just in maintenance requirements, but also can affect performance, which can avoid some performance problems. Usually SP_ is used for SQLServer system stored procedures, but some programmers will try to name it as a custom stored procedure. Below we will demonstrate how to use the policy to limit the SP_ prefix, so that it only belongs to SQ...
read(3361) comment(4)

Sogou The eighteenth chapter -- Based on the strategy management (1) -- the evaluation database attribute

Objective: this chapter includes: 1. Evaluate the attribute database 2, restrictions on database objects introduction: Policy Based Management (PBM) can help the DBA management one or multiple instances, and the PBM management database entity or other SQL server objects. PBM to assist DBA application or mandatory server object and database in accordance with the policy to operate. The concept is to keep in mind: 1, strategy: definition in the PBM database or server object rule. 2, conditions: Boolean condition is state value. 3,: in PBM, the target behavior pattern...
read(2454) comment(0)

Sogou The seventeenth chapter - configuration of SQLServer (4) - to optimize the configuration of the SQLServer instance

Sp_configure can be used for the management and optimization of SQLServer resources, and most of the configurations can be realized with the graphical interface of ManagementStudio SQLServer. Preparation: SQLServer to view the current instance configuration can also be achieved using the following query: SELECT * Sys.configurations FROM BY name ORDER The following are the results: machine performs the following steps: 1, statement to...
read(3541) comment(0)

Sogou In the seventeenth chapter, the configuration of SQLServer (3) -- the optimization of real time load"

Objective: in the first implementation of a query or stored procedure, create an execution plan and stored in the SQL server procedure cache memory. In many times, we will implement some simple procedures, just once, and create a stored procedure for these queries is a waste of memory resources. Due to insufficient memory, you may cause your cache overflow to affect performance. Before 2005, this was a big problem, in order to correct the problem. Microsoft SQLServer 2008 in the introduction of the optimization of the real-time query load. This feature is also in the 2012...
read(3639) comment(1)

Sogou The seventeenth chapter - configuration of SQLServer (2) - 32 and 64 bit system in the memory configuration

Objective: This paper about memory configuration for 32-bit and 64 bit systems, in SQL Server 2005 / 2008, DBAs tend to try and open awe to limit the memory. But, after SQLServer2012. This option will be abandoned, so you can't use the this way to control the virtual address space of a 32-bit instance. If you have a lot of memory on the server, you can only upgrade to 64 bit system. Here is the Microsoft given memory limit: although after 2012 has abandoned AWE, but about 32 bit systems...
read(7913) comment(0)

Sogou The seventeenth chapter - configuration of SQLServer (1) - SQLServer configuration for more processors

Objective: of the SQL server provides a system stored procedure, SP configure, can help you manage the instance level configuration. Microsoft recommends that you use the default configuration, but depending on the server, the different loads of the system and your usage, change the configuration may give you the benefits of performance. In the 32 bit and 64 bit systems, sp_configure will have some differences. We often see where the SQL server server also contains such as IIS, file server or domain controller these services or work.
read(5098) comment(0)

Sogou The thirteenth chapter - table and index partition (2) - uses the split to remove and loads the big data

Objective: most of the time need of large amount of data file or delete, and cycle loading large amounts of data to a large table, now to do a simple example, you often need to remove a large amount of data in the table of a large number of data. At the same time, you want to load a large number of data into this table, when the table data have tens of billions, this operation may consume a few hours, but if you have a partition table, then the implementation will be very effective. This paper will simulate the deletion of a quarter of the data, and load the entire quarter into an existing table, which uses a split (splitting), a merge (merging) and a cut...
read(2946) comment(0)

Sogou The thirteenth chapter - table and index partition (1) - use Left Range to partition table

Objective: if the data table data continued to grow, and the amount of data has reached billions of dollars or even more, the query and manipulation of data will be very difficult, in the face of very large tables, when simple additions and deletions to the operation will cost a lot of time, such as delete some data and rebuild the index operation, it will be difficult to achieve. In this case, the management and maintenance of query performance has become a challenge. In the past days, that is, 2005, you may need to use the partition view to handle large data amount of data, from the beginning of 2005, Microsoft introduced a new feature called table partition. Allows level split data into...
read(3461) comment(0)

Sogou The twelfth chapter - SQLServer statistical information (4) - statistical information on the filter index

Objective: from 2008, introduced the a enhanced non clustered index filtration index (filter index), can be used with the where clause of the statement to create a non clustered index to filter out unwanted data, reduced index maintenance overhead and storage space, improve query performance. Preparation: in AdventureWorks2012, there is a Production.WorkOrder table, will use this table to do demonstration. Steps: 1, create a non clustered index in Produc...
read(2635) comment(0)

Sogou The twelfth chapter - SQLServer statistical information (3) - found out of date statistics and processing

A predicate in the data distribution is the main source of information is based on the statistical information of the. If you do not know the specific data distribution, the optimizer cannot obtain estimated data set, and statistics does not need to return the data. After the columns to create statistical information, in the DML operations such as insert, update, delete, statistical information will be obsolete. Because these operations change the data, the data distribution. Need to update statistics at this time. In high activity in the table, the statistics may become obsolete for several hours. For static tables, can be...
read(3091) comment(0)

Sogou The twelfth chapter -- SQLServer statistical information (2) -- the influence of statistical information on the non - index key

Objective: index of performance always play a an important role. Actually, the query optimizer first check predicate statistical information before deciding what index. Under normal circumstances, the default will create an index on the index column to create statistics. But does not represent the statistical information on the non - index key to the performance is not used. If on the table all the columns are indexed, there will be database can't afford, also is not a good idea, including an index of all lega predicates used also is not a good method. Because the index will bring the load. Because the space needs to be saved...
read(2768) comment(0)

Sogou The twelfth chapter - SQLServer Statistics (1) - create and update statistics

Introduction: the statistical information of the query: so far, the selection index, maintenance index. If there is a suitable index and real-time update statistics, then the optimizer will select a useful index for query, because SQL Server optimizer is based on cost optimization. When in where and on column data needs to be displayed in the result set, if there is a real-time statistical information, the optimizer will choose the best way to perform, because the optimizer will get the details of the data from the statistical information. When you create an index, SQLServer creates statistics on the index column...
read(6984) comment(0)

Sogou The sixteenth chapter deal with lock, blocking and deadlock (3) -- using Profiler SQLServer to detect deadlock

preface: As a DBA, may often encounter a colleague or customer reflect often deadlock, the impact of the use of the system. At this point, you need to detect and deal with this kind of problem as soon as possible. Deadlock is caused by blocking of two or more transactions with each other. In this case the two transaction will wait indefinitely for the other person to release the resources for the operation. Below is a schematic diagram of a deadlock: This article will use Profiler SQLServer to track the deadlock.   dead work: In order to detect deadlock, we need to simulate the deadlock. This example will use two not...
read(7943) comment(12)

Sogou In the sixteenth chapter, the processing of lock, blocking and deadlock (2) - blocking and blocking query

preface: If a transaction is waiting for a resource that is locked for other transactions. This transaction has been blocked ". In turn, a blocking transaction, which locks the resource and causes the transaction to wait for another transaction, is called "the blocked transaction."". Long running transactions block other transactions and queries, making them wait for long time. In the heavy system, many times we will encounter a blocking problem, if a transaction is not completed because of blocking. Will cause some of the waiting chain. This article will introduce how to discover and solve this problem at once.   dead work: This...
read(3678) comment(2)

Sogou In the sixteenth chapter, dealing with locks, blocking and deadlock (1) - to determine the long run of the transaction

preface: Transaction is the main part of the OLTP system. It management data consistency and data concurrency issues, when multiple resources at the same time be read or modify the same data, SQL server through the locking mechanism to ensure that the data in the database is always in a valid state. In SQLServer, the lock manager is responsible for implementing these lock mechanisms. SQLServer provides different types of locks for different resource types, such as databases, files, objects, tables, regions, pages, and keys. When you use a transaction, you will still encounter problems caused by the transaction, which are usually due to the lock,...
read(3675) comment(0)

Sogou The sixth chapter: according to the implementation plan of performance optimization (3) - key search

preface: The last article of this series, introduce the related knowledge of the search key. With a clustered index on the table a bookmark lookup key lookup, the key value lookup for the SQL server query some non key column of data. The use of non clustered index key query does not find, but all the key for will be accompanied by a non clustered index appeared. Here special remind is key for always accompanied by nested loop association.   dead work:   The following will create a table, through the implementation of plans to see the effect of different key search. In order to produce...
read(4069) comment(0)

Sogou In the sixth chapter, according to the performance plan optimization (2) - look-up table / index scan

preface: In the majority of cases, especially a small amount of data returned from a large table, table scan or index scan is not a efficient way. These must find out and solve them to improve performance, because the scan will traverse each line, find the data in line with the conditions, and then return the results. This process is quite time consuming. In the process of performance optimization, the general focus is on: 1, CPU 2, Network 3, Disk IO And the scanning operation will increase the cost of these three kinds of resources.   Preparation work:...
read(3627) comment(0)

Sogou In the sixth chapter, according to the optimal performance of the execution plan (1) -- Understanding hash, merge, nested loop join strategy

preface: This series includes: 1, Hash, Merge, Nested understanding Loop Association strategy. In 2, program to find and solve the table / index scan. 3, introduced and found the key lookup and solve them in the execution plan.   For performance optimization, focus on the following issues: 1, for your environment to create a performance baseline. The performance of 2, monitoring now and find bottlenecks. 3, solve the bottleneck in order to get better performance.   An estimated execution plan is a description of how the query will be executed...
read(4593) comment(0)

Sogou The seventh chapter - DMVs and DMFs (4) - using DMV and DMF monitor disk IO

preface: The series of the last article, as a DBA, you must often focus on the problem of disk I / O, if there are problems, as soon as possible to analyze what the problem is. SQLServer also provides a number of columns and I/O related DMO to do monitoring. Introduced how to use DMO to monitor the performance of I/O system and find the I/O bottleneck. Through this paper, we can distinguish the different database I/O use model. Once you find a database of I/O is very high, you may need to consider the migration of the database to a separate disk, or in depth...
read(2873) comment(0)

Sogou The seventh chapter - DMVs and DMFs (3) - using DMV and DMF monitoring TempDB

preface: We know tempdb is a SQL Server database system and the daily operation of SQL Server rely heavily on the library. Therefore, it is particularly important to monitor the performance of TempDB. For a long time in the past, many people have ignored the importance of the TempDB and ignored its performance issues. This is not a good thing, because the performance of TempDB will affect the performance of other user database, so you need to always pay attention to the performance of TempDB. In polymerization, some sort of query...
read(2571) comment(0)

Sogou The seventh chapter - DMVs and DMFs (2) - using DMV and DMF to monitor the performance of the index

This paper continues to introduce the use of DMO to monitor, this is about monitoring index performance. Index is the key measure to improve the performance of query. Even if you have a proper index on your table, you need to maintain the task at all times.   SQLServer has specialized DMO to display index related statistics. Can help you analyze the performance of the existing index. Through these DMO, you can do it: The check index model The find missing index The useless search index The search index fragmentation The analysis index page allocation list This paper will...
read(2677) comment(0)

Sogou The seventh chapter -- DMVs and DMFs (1)

Introduction: From SQL Server 2005, Microsoft introduced a new characteristic of DMO (dynamic management objects), named DMO can be divided into DMFS (dynamic Functions Manage, dynamic management function (Dynamic) and DMVs (Manage Views, dynamic management view) two parts. These functions and views are used to find the SQLServer instance internal statistics to be used for performance monitoring. They provide real-time, internal work on the SQLServer...
read(17302) comment(3)

Sogou The fourth chapter - SQLServer2008-2012 resources and performance monitoring (3)

This paper is the last chapter of this series, monitoring the use of memory. Monitor the server's memory is very important thing, there are a lot of things will cause memory consumption. So check it regularly. This paper will use reliability and performance monitor to obtain memory related statistics.   dead work: Before you start, you should know about the counter you want to use: The Memory: Available Mbytes: provides the number of available memory system. The Memory: Pages/sec: shows how many pages are used to read or write disk...
read(5959) comment(0)

Sogou The fourth chapter - SQLServer2008-2012 resources and performance monitoring (2).

This article continues above, describes how to monitor the use of CPU preface: CPU is the most important resource in the server. In the database server, the use of CPU should always be monitored so that SQLServer has been in the best state. This article will use the reliability and performance monitor to obtain the CPU related usage statistics Reliability and performance monitor is an enhanced version of the past performance monitor tool. At the same time with all the features of the performance monitor. Performance counters provide statistical functions for various system activities. Hundreds of performance counters can be found...
read(5570) comment(0)

Sogou The fourth chapter - SQLServer2008-2012 resources and performance monitoring (1)

The performance optimization of the first step is found the problem, and found that the problems generally has two kinds: detection of emergency and routine problem detection, detection for the conventional problems, usually need to have a long-term performance monitoring as the basis.   Introduction of this series of articles Server performance monitoring CPU usage monitoring memory usage monitoring   preface: When due to performance problems caused by poor query writing, lack of necessary index or database level, by using the execute plan, DMVs/DMFs, SQL trace or data.
read(12080) comment(1)

Sogou In the third chapter, using the system function, stored procedure and SQLPERF DBCC commands to monitor SQLServer (3)

This article is the last part of this series. How to use the DBCC command to monitor the usage of the SQLServer log space.   preface:   Each database must have the transaction log. Transaction log records each DML operation, and is applied to the SQLServer database, if the recovery mode for FULL and often have DML operations, the log will grow very fast. When the recovery mode is simple, the log usually grows when the database is in transactional replication or merge replication. If the log is not a regular backup and the log file...
read(4175) comment(0)

Sogou In the third chapter, using the system function, stored procedure and SQLPERF DBCC commands to monitor SQLServer (2)

To undertake the above, this paper describes how to use the system stored procedures to monitor the system.   SQL server also also provides the system stored procedure used to monitor SQL server, access to detailed information about the current process, the session, request, and a locking. This article will demonstrate the system stored procedures to achieve these monitoring.   Scene: Sometimes you find application suddenly become very slow, often need awaiting a response from the database, then you need to quickly see whether the request is blocked or suspended.   dead work:   In this paper, will make...
read(3869) comment(0)

Sogou In the third chapter, using the system function, stored procedure and SQLPERF DBCC commands to monitor SQLServer (1)

Forgot to note: this series of articles from the "SQL Server Performance 2012 Tuning Cookbook Microsoft", will be launched in succession, but because of the need to work, not in order to stick out. This series consists of three parts: 1, Use statistical functions system (statistical function) to monitor the health of the system. 2, using system stored procedure to monitor the process and session SQLServer. 3,...
read(3327) comment(1)

Sogou When to use SET and SELECT as variable assignment

We often use SET and SELECT to replicate the variable, but sometimes, can only choose one to use, the following to look at these examples, this example uses the AdventureWorks database to do the demo. Return value through query: When you put the query returns the value paid variables, the set will accept the results (single valued) and pay a scalar value. But SELECT can accept multiple values returned by the query. Below to see the single and multiple values are examples of the use of SET:         USE A...
read(10792) comment(7)

Sogou SQLServer more than 2008 of the misuse of the database recovery method - log back

Source: Http://prog3.com/sbdm/blog/dba_huangzj/article/details/8491327 Http://prog3.com/sbdm/blog/dba_huangzj/article/details/8491327: often see someone accidentally deleted data, or misuse, especially the update and delete no where and shout dad shouted Niang. To err is human, wrong can understand, but can not indulge, this later, now the first to solve the problem. Encountered such a situation, usually do backup, or not to ask. ...
read(51439) comment(67)

Sogou RESOURCE_SEMAPHORE SQLServer wait state

summary: When an instance of SQLServer running very slow, should do some examination, such as checking the wait state. The best way is to start by establishing a performance baseline in order to make a performance comparison. When you find a comparison with the performance baseline, there is a memory pressure, then you need to find out what is the cause of the. You can check the status of the transaction, which is the most likely Resource_semaphore wait. Here is how to deal with this problem: When examining the affairs of all types of waiting, you may find R...
read(3331) comment(0)

Sogou SQLServer lists the columns and properties of each table

When you need to sort out a database to help the document is, you may need to list each table in the column and its properties. This may help in developing some interfaces or outsourcing to other companies. If you need someone to open Server Management Studio SQL (SSMS) to a view, is undoubtedly a kind of torture. Solve this problem can be considered using the catalog view system: sys.tables, sys.all_columns, sys.types Sys.tabl...
read(4761) comment(3)

Sogou Do you also forget to refresh the view?

Causes: Because of work reasons, I every few days to perform a number of developers over the script, the part is the new demand of the development, is part of bug fixes. Often contains a few hundred. I use tools batch execution and the system continues to run, then back and forth will have this or that kind of mistakes, which a, after inspection of the developers is because the view is not refreshed. I wondered for a long time, the view is not just a bunch of select statements? How to refresh? Do not change the table will not follow? To this end, I first made an experiment to find...
read(7239) comment(21)

Sogou Use SQLCMD to execute multiple scripts in SQLServer

summary:   As DBA, often provided by the developers of the SQL script to update the official database, but a more reasonable development process, when the script is submitted to the implementation of the DBA may have hundreds of SQL file, and the order of execution, as now I work in the company, more than a dozen customers, a library for each customer, but database structure, stored procedures, views, and so on are exactly the same, every time a script is executed (hereinafter referred to as the upgrade), if there are a hundred script, then they would have to performed sequentially over a thousand times, the workload is not a person can withstand. ...
read(21198) comment(3)

Sogou Server SQL high availability - log transfer (4-3) - use

The next article: Server SQL high availability - log transfer (4-2) - the deployment This article is one of the most important articles about how to use the log shipping and some matters needing attention. From the last article can be seen, in fact, the configuration is not difficult, it is difficult to be difficult in the event of a problem, how to deal with. These are 4 of the high availability of the same sex. Configuration will not be very difficult, but how to troubleshoot it.     Monitor log shipping: After configure log shipping and need to monitor, monitor backup, copy and restore operation. Any of these three classes have not...
read(3860) comment(0)

Sogou Server SQL high availability - log transfer (4-2) - Deployment

Before the continuation of the previous book, the book is connected to the next time. This chapter demonstrates the specific process of log shipping   dead work:   Because of the time, has installed 3 virtual machines, and in one domain:         SQL01: master server SQL02: secondary server SQL03: monitor server   Initial configuration: Experiment here is convenient, formal environment should be due to special account read and write permissions. Note that the read and write permissions here should not be SQL internal account, such as SA these, but sqlage...
read(4005) comment(3)

Sogou Server SQL high availability - log transfer (4-1) - An Introduction

In this paper, as a summary of the study, some of the content from online books and other books   What is log shipping?   SQL Server 2012 before (2012 appear AlwaysOn) and SQL server are four high availability cluster / cluster, log shipping, mirroring and replication). The main topic of this topic is to discuss the log transfer function. However, due to work reasons, can only talk about the use of the level, do not do too deep research.   Now, what is the first Shipping Log? ...
read(3786) comment(0)

Sogou SQLServer restore table level data

In recent days, the company's technical maintenance personnel frequently let me restore the database, because they are always less where conditions, resulting in delete, update has been unable to restore the consequences, plus those libraries are dozens of G. Recover less than ten minutes. Therefore, find some information and work summary, give a few methods for rapid recovery of table, and library is not, but remember, prevention total than mend good. In a production environment or development environment, often have some very important table. These tables store the core data. When the data in these tables are damaged, it is necessary to...
read(12128) comment(9)

Sogou A very useful function - COALESCE

A lot of people know the isnull function, but few people know the coalesce function, people will inadvertently used to coalesce function, and found that it is stronger and more powerful than the isnull, in fact so far, this function is indeed very useful, this article mainly explain the use some of the basic:   First look at the definition of books online:   Returns the first non null expression in its argument   Grammar:   COALESCE (expression [,... N])...
read(58866) comment(6)

Sogou View the history information of the SQLServer agent job

Dare not say is well known, but most people should know the SQL server agent job are stored in the SQLServer5 system database (master/msdb/model/tempdb/resources) MSDB, and due to the agent job running for a long time and there are many kinds of, so in general can see the MSDB size is often larger than other Kouga is greater. This paper mainly focuses on how to query the running time and the running time of the job. As a DBA, the periodic inspection operation is a very important task...
read(5232) comment(1)

Sogou Use Audit SQLServer to monitor the enable and disable the trigger.

The use of scene: Sometimes found that the business logic in the trigger is not executed, possibly because of the trigger logic errors caused by. But sometimes it is because some triggers are disabled.   As SQLServer enabled by default does not track or disable trigger. And disable the Trigger Disable (command) is not in the default tracking inside the capture. But can be tracked at the server level (not using Profiler) to get this information, capture SQL:StmtCo...
read(5334) comment(26)

Sogou Using SQLServer 2008 CDC function to achieve data change capture

Recently, due to the need of work, the 2008 CDC function, feel is good, the following order about the course of the study, although relatively rough, but basically can be used, if you have anything to add, please reply, I as the case to modify the original post, thank you. Background: Before SQLServer2008, the capture of data changes usually use the trigger, time stamp, and other inefficient and high cost functions to achieve, so many systems do not have to do the data changes or just monitor the core table. Since the introduction of the SQLServer2008 data change capture feature (hereinafter referred to as the...
read(19018) comment(45)

Sogou Database development, referential integrity -- the use of the Delete on cascade option in a foreign key

Original text: Http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012731?   Reference integrity in the design of the database need to pay attention to...
read(7006) comment(6)

Sogou Database management - Powershell - use the Powershell script to find files that consume the most disk space

The original: Http://www.mssqltips.com/sqlservertip/2774/powershell-script-to-find-files-that-are-consuming-the-most-disk-space/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=?...
read(4315) comment(0)

Sogou Performance optimization - statistical information - SQLServer automatic updates and automatically create statistical information options

Translated from the originals: http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/? UTM Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913 Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913 Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913 Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913...
read(12782) comment(2)

Sogou Database management - Security Management - to identify the SQLServer hollow or weak password login name

The original: Http://www.mssqltips.com/sqlservertip/2775/identify-blank-and-weak-passwords-for-sql-server-logins/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012925?...
read(3314) comment(1)

Sogou By configuring the Windows firewall to allow remote access to the database using the TCP/IP protocol

This article applies to: 2005, 2008, 2008R2 all versions In order to remotely access the SQLServer database through the TCP/IP protocol, you need to do the following: On the server running on SQLServer, we have to find the port on which SQLServer is listening and then add to the WIndows firewall. A: the need to add the SQLServer port to the firewall rules. Two: if you can not determine the connection string of the port or SQLServer did not listen to TCP...
read(7893) comment(0)

Sogou Log files grow

First understand SQLServer need to save the log records:   1, all without "checkpoint" log:   SQLServer (Checkpoint), ensure the timing of the implementation of the "dirty page is written to the hard disk. Did not do Checkpoint, may only be modified in memory, the data file is not synchronized. SQLServer to the hard disk in the log file records, while the abnormal restart after the re modification.   2, did not produce all submitted firm log and log records of follow-up...
read(6708) comment(2)

Sogou How to downgrade the SQLServer database from high to low?

Due to the widespread use of SQLServer2000, a lot of companies want to use the new SQLServer, which directly [separation / add] or [backup / restore] database, in different versions of the storage. Often encounter version is not compatible with the problem. A few days ago met from my native 2008R2 backup of a database restored to 2008 above times wrong: from the version of the operating 10.50.2500 (2008R2 is 10.50) and 10.00.1600 (2008 is 10.00) can be seen is not compatible with this version of the problem,...
read(77072) comment(21)

Sogou In Server Management Studio SQL can run the job but with the T-SQL run is failed

Question: In Server Management Studio SQL can run the job but with the T-SQL run is failed, and vice versa. analysis: The majority of this situation is the implementation of the context (Context) security issues. When executed in T-SQL, the SSMS statement is run in the current logon name. But the job in the Server Agent SQL is to Server Agent services (Server Agent SQL Service a SQL...
read(5733) comment(0)

Sogou Restore SQLServer instance connection

From: Http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012829?   Question: Open SQLServer2008...
read(6711) comment(0)

Sogou The difference between Delete and Truncate

Generally for no use of data, will be deleted, and usually used to remove the DELETE and TRUNCATE commands. For the conditional deletion, basically will use delete, of course, is not absolute, truncate can also be achieved, as long as the [without] delete the data into a new table, and then truncate the source table, then the data back or rename the new table can be. The following example mainly compares the whole table to remove the case of DELETE Difference between TRUNCATE and:   First, create a test...
read(3595) comment(1)

Sogou Effect of priority of SQLServer data type on Performance

From: Http://www.mssqltips.com/sqlservertip/2749/sql-server-data-type-precedence/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012814?   Question: I use in my application in the simple search...
read(4822) comment(4)

Sogou SQL Server connection TCP/IP

From: http://blogs.msdn.com/b/apgcdsd/archive/2012/02/24/ms-sql-server-tcp-ip.aspx TCP/IP basic working principle here is not a waste of saliva. Now this network era, who do not know TCP/IP ah. Don't tell me to say you have never heard of a partial TCP/IP, so you have to make up classes.   TCP/IP protocol has two basic things, one is the IP address, and the other is the port number. ...
read(10297) comment(1)

Sogou Using Buffer SQL in Server Ring 2008 to connect to the troubleshooting

From: http://blogs.msdn.com/b/apgcdsd/archive/2011/11/21/ring-buffer-sql-server-2008.aspx Server SQL 2008 contains a new feature designed to help solve the problem of particularly difficult connection. This new feature is the Ring Buffer Connectivity, which can capture every connection that is initiated by the server to close the record (server-initiated...
read(2677) comment(0)

Sogou Unable to use login SQL to log on to Server'Password - did not Match'SQL

From: http://blogs.msdn.com/b/apgcdsd/archive/2011/02/01/sql-login-sql-server-password-did-not-match.aspx Problem Description: in one machine on the Management Studio, always cannot use the SQL login to visit SQL Server. But if on other machines, the same login SQL can be used to log on to SQL...
read(1999) comment(0)

Sogou Server SQL connection problem - named pipes

From: http://blogs.msdn.com/b/apgcdsd/archive/2011/01/12/sql-server-1.aspx A. preface   In the process of using SQL Server in, users encounter the most connected problem. In this paper, the in-depth discussion of the SQL Server connection side of the issue on, hoping to help you solve the connection problem of SQL server.   SQL Server support many communication protocols, such as life...
read(10188) comment(0)

Sogou Server SQL 2008 (R2) stand-alone version of the prerequisites for installation

From: http://blogs.msdn.com/b/apgcdsd/archive/2012/03/07/sql-server-2008-r2.aspx To provide technical support to customers. It is found that the installation of SQL Server 2008 (many source R2) stand-alone version of the problem in the following several typical cases, and customers who know something, this is the SQL Server necessary for installation of prerequisites: 1.NET Framework 3...
read(5280) comment(1)

Sogou TFS:TF30042 database is full processing method

This morning, the company called, said TFS (Foundation Server Team) Microsoft source code management software can not be checked, the report is wrong: TF30042 database is full. After about half an hour of processing, basically good, and once again summed up: According to the prompt, I first check the disk space, found that there are dozens of G (the company's TFS database is relatively large, light compressed backup has more than 20G), enough to backup. Check after the hard disk space, and then check the database space, found that the log file is full, as usual...
read(4583) comment(2)

Sogou Blog column the first article - in my eyes DBA

In full-time DBA job a year over a month later, the opening of the CSDN blog column, in the first article, I talk about a year DBA career feelings, although I am a SQL Server DBA, but I think this paper is suitable for all the DBA, the way to this article as a man really DBA open end and guidelines: Why should there be DBA: For a large number of small companies, is to afford a DBA, but also not very necessary. So DBA this career is how to produce? I also don't understand, the personal guess is that when your application system and database arrive...
read(8047) comment(29)

Sogou Removing function calls can have better performance.

Original from: Http://www.mssqltips.com/sqlservertip/2727/removing-function-calls-for-better-performance-in-sql-server/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012726?...
read(2730) comment(0)

Sogou How to get the Server SQL index usage

Original from: Http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/   Question: The database has two large storage requirements: data pages and index pages. Understanding and viewing the actual data in your table is fairly simple. Know which columns are used, and what type of data is actually stored, by running some query examples. On the one hand, it is difficult to know the actual index...
read(3670) comment(0)

Sogou How to identify CPU bottlenecks in Server SQL

Original from: Http://www.mssqltips.com/sqlservertip/2316/how-to-identify-sql-server-cpu-bottlenecks/   Question: If we often encounter CPU bottleneck caused by SQLServer downtime, how to find and solve these problems?   Solution: There are many reasons to cause SQLServer to become CPU performance problems, because of the lack of resources...
read(4720) comment(0)

Sogou How to identify IO bottlenecks in Server SQL

Original from: Http://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/ Question: We may often encounter the SQLServer database frequently closed. After analyzing the memory and CPU usage, we need to continue to investigate whether the root causes in the I/O. How should we identify whether I/O has SQLServer related bottlenecks? ...
read(7360) comment(1)

Sogou Server SQL 2008 performance troubleshooting (four) - TempDB

And then the last chapter: I/O TempDB: A global database is tempdb internal storage and user object and a snack table, object, produced in the operation process of SQL server stored procedure data. In an instance of SQLServer, there is only one TempDB. So easy to become a bottleneck in performance and disk space. TempDB may become overloaded due to space availability and excessive DDL and DML operations. This can lead to slow or even failure of the application running slow. ...
read(8168) comment(1)

Sogou Server SQL 2008 performance troubleshooting (three) - I/O

And then the last chapter: CPU bottleneck I/O bottleneck (Bottlenecks I/O): The performance of SQLServer is heavily dependent on the I/O subsystem. Unless your database is fully loaded into the physical memory, SQLServer will continue to move the database files from the cache pool to move out, which will cause a large number of I/O transmission. Similarly, log records must be written to disk before the transaction is declared to have been submitted. Finally, SQLServer uses tempdb for many reasons, such as storing temporary results, sorting, and keeping the row version. So a good...
read(8768) comment(0)

Sogou Server SQL 2008 performance troubleshooting (two) - CPU

Undertake on a: Server SQL 2008 performance troubleshooting (a) - An Introduction Explain, CSDN blog editor is very not human, I have lined up in the word version, posted on the chaos is not like a. Suggest CSDN to improve this part. Also please pay attention to the content do not pay attention to the layout. Also in the process of translation I also compiled a train of thought, so it seems to be very willing to translate, although there are some enjoyment, but sharing to everyone is a good thing CPU bottleneck: CPU bottleneck may be caused by the lack of hardware resources required for a load. But...
read(12001) comment(2)

Sogou Server SQL 2008 performance troubleshooting (1) - Introduction

Note: I spent a lot of time to work, no plagiarism, but allowed to reprint, please indicate the source. Due to the length of the length of time, can not be a blog all finished, but also not so fast all translated, so by chapter. Due to my limited level, the translation must problems, in order not to cause misleading, in each at the end of are attached to the original, for your reference, hope my problems are pointed out, in order to improve. thank you In addition, this paper for a slightly experienced database developers or DBA, beginners may not understand. Please forgive me this Author: Agarwal Sunil...
read(6271) comment(0)

Sogou T-SQL technology to collect - delete duplicate data

In the work and the interview, often appear to query or delete duplicate data, if there is a primary key, it is also easy to handle a little, if does not have a primary key, it is trouble. When there is no auxiliary keys on a table, if the use of SSMS interface to delete, will report the fault (note that I test environment is 2012, so the interface may be different, but the results are not any effect): Because the tables are created after insertion of data is not judged. But in the delete, in order to ensure the consistency of the database, RDBMS will...
read(4283) comment(0)

Sogou T-SQL skill Collection - split string

In development, a lot of times are needed to handle the operation of the split string. The following collection of several methods for everyone to share, one of the comma can be changed to a variety of needs, but not for a variety of symbols at the same time there are examples. You need to add: explain: Converts the string to a normalized data table, you can use a variety of methods to achieve, such as front-end processing, cursor, database loop function, are commonly used skills. To deal with these problems, the better way is to use the SUBSTRING function to remove the string, and then use the CHARINDEX function. last...
read(3881) comment(0)

Sogou T-SQL use case - the results of the data in front of automatic fill 0

Phenomena: In the process of development, often need to digital and string. In the process of conversion, it may be required to put 1 programming 00001, such a format. There are many ways to achieve this, this paper mainly provides a custom function to achieve   explain: General this treatment mainly uses CONVERT or CAST to achieve. The idea is first converted to a string and then the number of 0 is not enough, and finally using the RIGHT function, from the right to take out the required length.   Case: In this case...
read(7317) comment(5)

Sogou Use a T-SQL statement to query the data table space and the number of rows

To find the space occupied by the database and the number of rows in the database. You can use the sp_spaceused with the name of the data table. It can produce the table space and the number of rows of existing consumption. Such as: ADVENTUREWORKS USE GO Sp_spaceused EXEC [Sales.SalesOrderHeader] GO     But if the database contains thousands of data tables, how can we use an SQL statement to achieve?   resolvent: First, dynamic...
read(3315) comment(0)

Sogou T-SQL development - ID processing articles

The difference of Identity, Timestamp and Uniqueidentifier in the database self increasing ID function: Problem phenomenon: General serial number generation, for the general programmer, are using the T-SQL command to achieve. First read the table in the greatest need, and then add a, and then plug back into the database, this is a very dangerous. Because if the transaction mechanism is not handled well, it will appear in the same time to obtain the same serial number. The results can be imagined. In order to avoid this situation, SQLServer has provided a certain mechanism to assist in the process of handling. ...
read(7161) comment(8)

Sogou Using CASE expression instead of dynamic SQL in Server SQL

Translation from: Http://www.mssqltips.com/sqlservertip/1455/using-the-case-expression-instead-of-dynamic-sql-in-sql-server/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012620?...
read(6294) comment(0)

Sogou Server SQL index column - is it really okay

Http://www.mssqltips.com/sqlservertip/2718/sql-server-index-column-order--does-it-matter/? Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012619? Question: When setting the index of the table, there are...
read(4721) comment(0)

Sogou T-SQL solve the problem of collection - data encryption (2)

Question three, how to allow the user to specify the data table for the Truncate operation? Truncate at the big table the delete operation, significantly more than the delete statement is faster and more effective, but because it does not need to store the log, and must be a full table delete, so cause data can not be recovered. It also shows its danger. However, performing the truncate table has these, system administrators, and the DB owner, the DB ddladmin which a high role permissions to perform...
read(2636) comment(0)

Sogou A variety of methods that are read only by setting some tables in the SQLServer database

Translation: self http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/? UTM Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012614 Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012614 Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012614 Utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012614...
read(5367) comment(1)

Sogou T-SQL solve the problem of collection - Data Encryption

The following code has been tested on the SQLServer2008 sample database Question 1: how to encrypt and decrypt the data, to avoid the user to steal confidential data? For sensitive data, such as passwords, card number, generally can not be used to store the normal value. Otherwise there will be security risks. Previous encryption and decryption have front-end application to complete. And the database can only be encrypted can not decrypt. To provide data encryption and decryption database from 2005 aspects. In fact, the main way is the following: 1, by CON...
read(4626) comment(0)

Sogou Server SQL date related

For developers, date processing may be simple, may be difficult. Combined with his past experience in the development and integration of online examples, summarizes some date related operations, for their own standby and for everyone to share:, date type: for SQL Server 2008 (since 2000 and 2005 has been a little signs of being eliminated, so in this not as over that, and their own work is 2008R2. It is not guaranteed to be used in the past 08 years, the date type is: data type format range precision storage size (in bytes) user defined seconds of the small...
read(10688) comment(12)

Sogou Talking about database connection

Must be clarified, although the article is I summarize the, but a lot of knowledge indeed is not up to me to research and analysis, through listening to training, reading and practice are summarized, on the one hand in order to stand on your own to problems in the future to solve, on the other hand also hope encounter problems with the same friends can get some inspiration from. So the knowledge in the article may appear in a lot of places. We often encounter a lot of connection problems, and programmers tend to think that the connection database only need to simply connect to the openconnection, operation, close, but a simple connection action,...
read(17264) comment(4)

Sogou Database Engine Tuning Advisor

Database performance is largely determined by the appropriate index on the table. However, work load and data changes at any time, the existing index may not be entirely appropriate, may require a new index. However, it is very difficult to determine the index, because of a query to improve the role, but will have a negative impact on other queries or other operations. SQLServer provides a database engine Consultant (DTA) tool to help confirm a set of optimal indexes for a given workload. And do not need to understand the structure of the database and SQLServer. It can also provide a small portion of the problem...
read(3134) comment(5)

This award - Windows PowerShell Practical Guide (Second Edition)

Why do you want to learn PowerShell? Windows users are accustomed to using the graphical interface to complete the work, because GUI can easily achieve a lot of functions, and do not need to remember a lot of commands. Makes the short time to learn a tool to become possible. But unfortunately, GUI and can not bring to enhance the efficiency of report, if you spend five minutes in the active directory to create a new user (generally need to fill out a lot of information), then create a new user, usually not be faster, then the new 100 new users will spend 500 minutes, usually without any way to make us input.
read(22947) comment(19)

Server SQL performance optimization and management of art accessories download address

First of all thank readers for humble support and buy the SQL Server Performance Optimization and management of art ". Before the press...
read(3882) comment(3)
A total of 234 12 pagesOne Two Three Four Five ... next page Shadowe
    personal data
    • visit:1140124 times
    • Integral:Thirteen thousand two hundred and thirteen
    • Grade:
    • Rank:600th name
    • original:102
    • Reproduced:37
    • Translation:95
    • Comments:473
    My micro-blog
    Blog column
    Latest comments
    Quantum statistics