Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Featured Script
Redgate Data Masker
The Voice of the DBA
 

Learn from the Troubles of Others

Some of us have experienced lots of strange issues while working with SQL Server. Some from poorly configured or coded systems, some from heavy workloads, and some from bugs in the code. Encountering and solving those problems, even if self-induced, is one way that we have learned about the best ways to ensure SQL Server performs extremely well under many situations.

Others of us have had few issues and found SQL Server to be a very stable and solid product. In fact, I often find someone posting about an issue has been running an instance for years without any issues until one day something breaks or reaches a tipping point. I've seen this with numerous instances that were installed with plenty of disk space and without log backups. They may run for a year or more before logs fill and cause issues.

It can be hard to plan for the unknown disasters that might be lurking in your system, but one thing that might help you think more widely about potential issues is reading posts about the issues others have encountered. Certainly plenty of people blog about these, and we often put out in our Database Weekly newsletter, but there are other good sources.

The CSS SQL Server Engineers write some interesting posts about issues they've solved. Availability Groups are important and harder to configure and manage than many people realize. I caught a really interesting post about strange behavior under high workloads. This was with In-Memory technology, which I am starting to see more people experiment with implementing. This is the type of post that might give you pause, but also help you plan for and be aware of an issue that could occur.

In this case, it's a bug and a CU fixes the issue. Without knowing this, you might open your own case and spend time debugging the issue. Reading an article like this might trigger you to go back and check on it if you experience the issue. The bigger advantage I see here is there is a lot of information about how to measure and understand the performance of various parts of an AG system with code and linked articles here. While others might tell you there's a CU to fix a specific issue, the knowledge you gain about how to dig into the performance of your AG setup could help you solve lots of other types of issues.

Keep learning and keep reading. There's lots of amazing SQL Server information on the Internet for you, and we're happy to keep bringing some of it to you at SQLServerCentral.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Redgate SQL Source Control
 
  Featured Contents

Collections

bsahlean 61286 from SQLServerCentral

Problem The usage of a collection of integers in some scenarios is considered to be optimal because of the overhead involved by the creation of another user table used just to store numbers, which is the most common solution. What are collections of integers? They are lists or arrays consisting of integers. The number of […]

Using Custom Deployment Scripts with SQL Compare or SQL Change Automation

Additional Articles from Redgate

Phil Factor describes how custom pre- and post-deployment scripts work, when doing state-based database deployments with SQL Compare or SQL Change Automation, and how you might use them to, for example, add a version number to the target database, specify its database settings, or stuff data into some tables.

Merging tables in Power BI dataflows with PRO subscription

Additional Articles from SimpleTalk

Power BI dataflows is a powerful self-service ETL tool. However, some features demand the use of Power BI Premium, way more expensive than the PRO version and sometimes inaccessible. In this latest blog post Dennes Torres explores the subject.

How to make your 2020 monitoring strategy a success

Press Release from Redgate

Join Redgate’s Arneh Eskandari and BMW’s Tony Maddonna for a free webinar on Nov 20: How to make your 2020 monitoring strategy a success. Learn how to prepare for successful estate growth, expected trends for the year, and the role monitoring plays in engabling growth and stability in your organization.

From the SQL Server Central Blogs - Build a SQL Cluster Lab Part 3

Ryan Adams from Ryan Adams

You are going to create a multi-subnet Availability Group in Part 3 of our series on how to build a SQL Cluster Lab. First you give the Cluster Name...

From the SQL Server Central Blogs - What SQL Server Agent Alerts Do I have setup?

SQLPals from Mission: SQL Homeostasis

I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy.I needed a query (DMV) to get details on what...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Leaving SQLCMD

I type SQLCMD to connect to my server, and use the "q" parameter. I end up with a 1> as shown here. SQLCMD command prompt What do I type to exist SQLCMD?

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

More Computed Column Indexes

I add this column to my table in a SQL Server 2017 database.

ALTER TABLE dbo.Activities
ADD ProdOneCount AS CASE
                        WHEN ProductID = 1 THEN
                            CallCount
                        ELSE
                            0
                    END;
GO

I now want to create this index.

CREATE INDEX Activities_ProdOneCountThreshold
ON dbo.Activities (ProdOneCount)
WHERE ProdOneCount > 50;

What happens when the CREATE INDEX runs?

Answer: An error occurs as the computed column cannot be used in the filter definition.

Explanation: This index errors out as the computed column cannot be a part of the filter. Ref: Indexes on Computed Columns - https://docs.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns?view=sql-server-2017

Discuss this question and answer on the forums

 

Featured Script

Create a view to Alter a Table and Add three Columns

Peter Heller from SQLServerCentral

I am teaching a database class at Queens College. The project entails the truncation of all of the data and loading new data into the  existing database "BIClass" from a single of the flattened data. One of the tasks of the project was to add these three additional columns to each of the tables (AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps): […]

ALTER TABLE FullyQualifiedTableName add [UserAuthorizationKey] [int] not null DEFAULT(-99)
ALTER TABLE FullyQualifiedTableName add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())
ALTER TABLE FullyQualifiedTableName add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())

select concat(t.TABLE_SCHEMA,'.',t.TABLE_NAME) as FullyQualifiedTableName
, t.TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES as t
where t.TABLE_SCHEMA in ('CH01-01-Dimension','CH01-01-Fact') and t.TABLE_TYPE = 'BASE TABLE'

CREATE VIEW [Utils].[uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps]
/*
ALTER TABLE FullyQualifiedTableName
ADD [NewColumnName] [Datatype | User Defined Datatype]
[not null | null]
DEFAULT(Default Value for NewColumnName )
*/
AS
SELECT t.TABLE_SCHEMA AS SchemaName,
CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
1 AS SortOrder,
CONCAT(
'ALTER TABLE ',
t.TABLE_SCHEMA,
'.',
t.TABLE_NAME,
' add [UserAuthorizationKey] [int] not null DEFAULT(-99)'
) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION
SELECT t.TABLE_SCHEMA AS SchemaName,
CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
2 AS SortOrder,
CONCAT(
'ALTER TABLE ',
t.TABLE_SCHEMA,
'.',
t.TABLE_NAME,
' add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())'
) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION
SELECT t.TABLE_SCHEMA AS SchemaName,
CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
3 AS SortOrder,
CONCAT(
'ALTER TABLE ',
t.TABLE_SCHEMA,
'.',
t.TABLE_NAME,
' add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())'
) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
GO

SELECT v.SchemaName
, v.TableName
, v.FullyQualifiedTableName
, v.SortOrder
, v.AlterFullyQualifiedTableNameAddingColumn
FROM Utils.uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps AS v
ORDER BY FullyQualifiedTableName, SortOrder

----Sample before and after of the execution

------ Before Adding the three columns

----CREATE TABLE [dbo].[Digits](
---- [digit] [INT] NOT NULL
----) ON [PRIMARY]
----GO

------ Add the three columns

----ALTER TABLE dbo.Digits add [UserAuthorizationKey] [int] not null DEFAULT(-99)
----ALTER TABLE dbo.Digits add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())
----ALTER TABLE dbo.Digits add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())

------ After Adding the three columns

----CREATE TABLE [dbo].[Digits](
---- [digit] [INT] NOT NULL,
---- [UserAuthorizationKey] [INT] NOT NULL,
---- [DateAdded] [DATETIME2](7) NOT NULL,
---- [DateOfLastUpdate] [DATETIME2](7) NOT NULL
----) ON [PRIMARY]
----GO

----ALTER TABLE [dbo].[Digits] ADD DEFAULT ((-99)) FOR [UserAuthorizationKey]
----GO

----ALTER TABLE [dbo].[Digits] ADD DEFAULT (SYSDATETIME()) FOR [DateAdded]
----GO

----ALTER TABLE [dbo].[Digits] ADD DEFAULT (SYSDATETIME()) FOR [DateOfLastUpdate]
----GO

More »

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Development
how can i see the last 100 select statements that were run by any user - how can i see the last 100 select statements that were run by any user against a particular table?
Identity column as Primary Key - good or bad? - I'm building a web app to maintain some data, which is mostly flat but will benefit from having some static look-up tables.  Typically these look-up tables with have at most a dozen rows and are unlikely to change over time. Consider the two different DDL scripts.  The first creates a look-up table CustomerType with an […]
SQL Server 2016 - Administration
DB goes into not sync / suspect after a manual failover - After the patching, one of the database goes into Not synchronizing / suspect state. I resumed a data movement manually. It goes into recovery pending to suspect. This is from the log file 'DB has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, […]
SQL Server 2016 - Development and T-SQL
One table or two? - I have an outside company that will be sending me 2 files each month.  File 1 has approx. 280 columns, File 2 has around 330.  The first 150 column names on each table are exactly the same.  The remaining columns on each file are unique to that file(no overlapping column names).  There is no one-to-one […]
How to apply rank when fetching data from table using sql query? - Hi Everyone, I need your help to get the required results. I want to get a machine status every 5 minutes. here how to apply rank as the sequence number for given resultset. the Rank should be a mentioned snapshot. Thanks in advance for your support   Sample Script select Starttime, Endtime,Status as machineStatus,datediff(minute,starttime,Endtime)as diff […]
list to table, versus delimitedsplit8k - Dear reader The list to table is build around a while loop, one item is taken of the list and put in a table, the list is shortened and the while loop is repeated. On seeing this construction I thought this would be a good candidate to be replaced with the delimitedsplit8k function. So to […]
Development - SQL Server 2014
repeating block of records - I want to look for blocks of records repeating in a table.   pattern is unknown...to keep it simple - table X can have up to 100 records   in the example below: The block: A,B and C is repeated 2 times I don't care about (A and B) or (B and C) repeated twice...I […]
SQL 2012 - General
Compare two databases - Hi ,   What are the parameters to compare two databases if migrated from source to destination .  
Need of database - In order to structure and layout 50GB of data which includes pdf files, videos, images, etc, which is the better way to implement? Using sql or not? Please can you tell me the pros and cons of it. Thank you. This is for an intranet site development!
SQL Server 2008 - General
Backup file size 3X after shrinking the log file? - Here's a head scratcher - I've not seen anything like this in 20+ years of DBA life! I received a SQL Server 2008 R2 backup file to restore.  There wasn't enough disk space on the server so I moved the file to my workstation to look at it.  After restoring the database, the transaction log […]
Using crypt_gen_random to generate unique Serial Numbers - Trying to generate unique serial numbers with alphanumerics (A-Z,a-z,0-9) and no special characters.Used below code where sLength was min 10 and max 12 as defined in front end. declare @sLength tinyint declare @randomString varchar(50) declare @counter tinyint declare @nextChar char(1) declare @rnd as float set @sLength = 3 set @counter = 1 set @randomString = […]
OPENQUERY is not inserting identical rows - Hi, Thanks in advance for any help. I am trying to insert into a sql table using data from an oracle server using a linked server. I have noticed it ignores any lines which are identical. However I do not want to ignore these and would like the query to insert the rows. I have […]
SQL Azure - Development
Azure file copy creating a directory to hold the renamed file - Hi All, I'm pretty new to azure and am hitting the following problem. I've got a notebook that creates an output csv file. The file gets named to the default Part-0000-tid and I want to rename it to something more sensible. The code below shows what I've done. The problem is it creates a subfolder […]
SQL Azure - Administration
Metric CPU show 100% , but Query Performance Insight show only 60% same period - Hi, I am investigation DTU spike for period specific period of time In my case  time period  in question between 08:00 PM and 11:00 UTC Azure portal ->db ->metric -> Cpu % avg  show that I have 100% usage of CPU  (db DTU =  1750) between 08:15 PM and 08:45 PM Azure portal->db-Query Performance Insight-> […]
Integration Services
Dynamically execute SSIS package. - I am trying to figure out how to dynamically execute SSIS package based on file path that are stored in the table. I found some examples online, it won’t work for me for some reason.  The path could be something like this \\ServerName\\SubFolder\Name.DTSX. Most examples I found online has something to do with SQL Server […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -