Dedicated SQL Server Hosting

Want dedicated SQL Server hosting? The following guide may help in evaluating SQL Server Hosts. SQL Server Azure – SQL Server Azure is the Microsoft SQL Server Cloud computing solution that allows you to remotely administer SQL Server via you SQL Server Management Studio. While it is difficult to figure the pricing model, it is obvious it is competitive, as these are the times to gather as many clients as possible. This network also makes use of a CDN (Content Delivery Network), and you can use SQL Server Management Studio to administer your site. iWeb – This is the host I use. Yes I use them for WordPress hosting. Why do I host a SQL Server site on WordPress? Basically because I believe in using the right tool for the job. What I have to say about this host, is that they are one of the most reliable hosts according to netcraft that there is. According to Netcraft iNetU is one of the most dependable SQL Server Hosts there is. The following link is from May 2010, however they have been reliable even since then. Rackspace is an undeniable source of SQL Server hosting. While their prices are expensive, their support is extensive and they have superior customer service. Click here check out their SQL Server hosting. Continue reading ...

Audit Tables Using XML Schema

Auditing tables can be a complicated procedure if you require a new table for every table being audited. Fortunately the new XML datatype can help to automate this procedure of auditing multiple tables into a single table. The following trigger template can be used to audit a table into a single table. First let’s create the table needed to hold the audited values: [cc lang=”sql”] CREATE TABLE [Log].[AuditTableHistory]( [HistoryID] [int] IDENTITY(1,1) NOT NULL, [TableSchema] [varchar](10) NULL, [TableName] [varchar](100) NULL, [AuditValue] [xml] NULL, [DateCreated] [datetime] NULL, CONSTRAINT [PK_AUDITTABLEHISTORY] PRIMARY KEY CLUSTERED ( [HistoryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] — Now let’s create the trigger CREATE TRIGGER [Audit].[Trg_Schema_Table] ON [Schema].[Table] AFTER UPDATE,DELETE AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SET NOCOUNT ON; DECLARE @AuditValue xml SET @AuditValue = ( SELECT TOP 1 * FROM DELETED FOR XML AUTO ) INSERT INTO Log.AuditTableHistory ( AuditValue, TableSchema, TableName, DateCreated ) SELECT AuditValue = @AuditValue, TableSchema = OBJECT_SCHEMA_NAME(parent_obj), TableName = OBJECT_NAME(parent_obj), DateCreated = GETDATE() FROM sysobjects so WHERE = @@PROCID END GO [/cc] We can apply this trigger to any table we want just by changing the “ON” statement. It will work for any table. The only difficulty comes in when we change the schema for a particular table. When this happens, we need to know the date/time the table schema changed so […]

When to use the XML Datatype

When I first heard of the XML Datatype, I had mixed feelings. The reason is because from a traditional approach, all data structures can be represented using the classical entity relationship model. For me, a problem arises when trying to represent “documents”. When I say documents, I don’t just mean things like “Word Docs”. What I mean by that is data structures that mean nothing when the data is broken up into segments, however when represented in a whole document, that’s where the only relevance comes in. An example of this is a contract. It is possible to take disparate pieces of a contract and break it into separate rows and columns according to the contract terms. However, when you break a contract up into individual pieces, it means nothing when it’s separate. It’s context is only meaningful when the pieces of the contract are together. This is an example of when to use the XML Datatype. I recently wrote a rules engine, where I created all the rules using XML. When I showed some people, their immediate reaction was, “why can’t we break these individual parts of the rule into a table”. My answer was, “because separated, these individual rule items mean nothing, there is no reason to separate them. They only mean something when they are put together because that defines the rule”. In other words, the entire rule is atomic, there is no reason whatsoever to break it apart. When it is separate it has no meaning. […] Continue reading ...

Find Resource Usage by Application

Often times when troubleshooting, it is helpful to know how much resources a particular application is requesting from your SQL Server. The easiest way to get this information is by using the connections and sessions DMV. These DMV’s when combined are powerful. Here we will query the aggregate connection information. The values below represent an aggregate value of all connections that are currently established by that application. [cc lang=”sql”] SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name FROM sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id GROUP BY program_name ORDER BY cpu DESC [/cc] Note that the above query does not break down the values per user. In order to do that, we need to create an additional grouping condition (login_name). [cc lang=”sql”] SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name ,LoginName = ses.login_name FROM sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id GROUP BY program_name, ses.login_name ORDER BY cpu DESC [/cc]

Find Most Executed Stored Procedures

An important step in optimizing a system is to take a holistic approach and look at stored procedures that are called very often. These procedures can often be the backbone of a system. Sometimes optimizing these procedures can have a dramatic effect on relieving the physical bottlenecks of the system and improving end-user experience. The following DMV query shows the execution count of each stored procedure, sorted by the most executed procedures first. [cc lang=”sql”] SELECT DatabaseName = DB_NAME(st.dbid) ,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid) ,StoredProcedure = OBJECT_NAME(st.objectid,dbid) ,ExecutionCount = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = ‘proc’ GROUP BY cp.plan_handle ,DB_NAME(st.dbid) ,OBJECT_SCHEMA_NAME(objectid,st.dbid) ,OBJECT_NAME(objectid,st.dbid) ORDER BY MAX(cp.usecounts) DESC [/cc] These execution counts are an aggregate from the last time SQL Server has been restarted. Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!