Today, I am going to be starting a series of articles on temporary tables. I will be focusing on derived tables. A derived table is a sub-query that is used to as input to the outer query. An table alias should be used outside the parenthesis to reference the inner query. I think of derived tables as a cross between temporary tables and dynamic views that go away after the query executes. The first example of a derived table creates the car ownership table for person a. Please see the…
Testing Alerts
Having set up system and performance alerting, it is now time to test those darn alerts to make sure they work! Alerting can notify a operator via email, paging, or a net send messages. Now adays, most people have a smart phone, not an old fashion pager. Therefore, I usually create a Active Directory group that has both the regular company email of the DBA group and their cell phone eqivalents which are usually the phone number @ provider name dot com. Today, I am going to talk about two…
Performance Alerts
Today, I am going to talk about setting up performance alerts so that you can notified when a server has issues. Basically, there are two types of performance alerts, SQL Server and WMI. I am going to use the sp_add_category system procedure to define a peformance category.
|
1 2 3 4 5 6 7 8 9 10 |
-- Add a custom category for alerts EXEC msdb.dbo.sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'[Alert By Performance]' ; GO -- Show Alert categoryes EXEC msdb.dbo.sp_help_category @class=N'ALERT' GO |
The sp_add_alert stored procedure is used to create the alert and the sp_add_notification stored procedure is used to associate the alert with an operator. This process is used below to show examples of both SQL and WMI performance alerts. The first type…
System Alerts
This article assumes that you configured both database mail and system operators. Please see my previous articles if you have not done these tasks already. Today, I am going to talk about setting up alerts for system error messages that might occur with your SQL server database. Since alerts have been around for a long time, most of this information is backward compatible with older software versions. The database engine defines the importance of an error messages with a severity level. Low importance errors are defined by levels 0 to…
Defining Operators
SQL Server supports the notification of administrators through operators. Operators are aliases for users or groups that can receive notifications when jobs complete or alerts have been raised. Each SQL Server instance can have its own set of defined operators. Today, I am going to walk you thru the following tasks in Transaction SQL. Adding an operator Updating an operator Deleting an operator Defining a fail-safe operator To define an operator, one needs to know the email address of the user and what time he is responsible for the SQL…
Database Mail – Part 1
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. It is very important to configure mail on any server that you manage. Error and performance alerting as well as SQL agent job outcomes depend upon this service for notifications. I am going to copy a paragraph directly from Microsoft TechNet since I can not say it in any better in my own words. Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure…
DBA – Introduction
Today, I am going to start writing another topic thread for Database Administration (DBA). I do alot of DBA work among many other things at my current job. I am going to cheat a little by copying a list from a renown SQL Server MVP named Paul Randal. This list was published in a TechNet Magazine article in November, 2010. To be a succesful database administrator, you should start working your way through the following tasks (secrets) to manage choas at your organization.
|
1 2 3 4 5 6 7 8 9 10 |
10. Take Inventory 9. Standardize Configurations 8. Understand the I/O Subsystem 7. Create a Customized Maintenance Plan 6. Ensure the Security of Your System 5. Get on Good Terms with Your Developers 4. Develop a Comprehensive Disaster Recovery Strategy 3. Take and Test Regular Backups 2. Monitor and Maintain Performance 1. Know Where to Find Information |
In the coming weeks, I am…
Selecting Data – Part 4
Today, I am continuing our exploration of the SELECT reserved word by talking about sub-queries. A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. Sub-queries can be used anywhere an expression is allowed or can be nested inside another sub-query. I will be using the AdventureWorks Sample database supplied by Microsoft during this talk.
|
1 2 3 4 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- Use the correct database USE AdventureWorks2008R2 GO </span> |
One usage a sub-query is with comparison operators in the where clause. The example below returns all the sales people by name and id…
Selecting Data – Part 3
The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. Many of the functions used in aggregation have statistical meaning such as minimum value, maximum value,…
Selecting Data – Part 2
The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using joins and unions. Six known join types exist in SQL Server. The definitions are based upon joining TABLE A (left) with TABLE B (right). Inner Join – Include…