SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

XEvent Events

The Voice of the DBA

Daily Coping Tip

Let someone you love know how much they mean to you

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Methodical Testing

Performance tuning is a challenge for many data professionals. Often, it's also a task that we struggle to find time to perform in many environments. Developers have new features to build and DBAs have plenty of other work. It's usually done in a crisis

Netflix is always looking at performance, since their customers and audience are very sensitive to delays. They wrote a post on examining the performance of a potential technology change, which was a good look at different ways they test the effects of something new. In his case, they talk about the changes they were considering for networking stack. There is some technical discussion of why, but the interesting part of the piece, for me, was the A/B testing section.

They planned an experiment, conducted it, and then measured the results. This wasn't just a test for a developer workstation, which I've seen most people do. This was a test with half a million users. Netflix has over 150mm users, so this isn't a significant number, but it's also not a tiny number. It's enough to look for potential issues, though I'd hope they'd expand this to a 2-5% of users to verify their results.

Too often I see developers counting on their workstation test of a very small set of data in trying to determine if their approach makes sense. I do believe that is a good place to start, but before getting too far along the software development stack, some significant scale test ought to occur, with repeatable measurements. If you want to ensure your auditing trigger or update code or anything else that might impact lots of users will cause issues, test on a larger set of data. Repeat the rest and verify that your results make sense.

Testing is a skill. A bit of an art, but very much a science. You should make sure you use test harnesses that are large once you think your code works, just to be sure it does. If you want ideas on how, Jeff Moden and Dwain Camps have a few articles that might help.

Steve Jones - SSC Editor

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

Featured Contents

Dynamic SQL Going Haywire

Kanishka Basak from SQLServerCentral

Those who work on various databases, or even those who do not, must have come across the term, Dynamic SQL. Dynamic SQL is basically a programming technique that enables you to build SQL statements dynamically at run time. It allows you to create more general purpose flexible SQL statement because the full text of the […]

How to Attach a SQL Server Database without a Transaction Log and with Open Transactions

Steve Jones - SSC Editor from SQLServerCentral

You want to attach a SQL Server database that does not have the transaction log files and get the following error: "The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure." In this tip I will help you to successfully attach the transaction log to the problematic SQL Server database.

From the SQL Server Central Blogs - A Deep Dive on Clustered Index

Brahmanand Shukla from SQL Server Carpenter

This article will follow a practical approach to choose the right candidate for clustered index. It'll investigate through the best practices recommended for clustered index, with the help of...

From the SQL Server Central Blogs - SSIS Azure Blob Source Text Qualifier Issue

ChrisJenkins from Chris Jenkins' Blog

The issue When migrating the solution I’m currently working on to Azure eight months ago I was initially looking at transferring the SSIS packages that performed the ETL. We...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	XEvent Events
What is an event in Extended Events (XEvents)?
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)

Getting Dataframe Metadata

I have loaded a dataframe in Python with this code:

>>> import numpy as np
>>> import pandas as pd
>>> sales = pd.read_csv('sales_data.csv', parse_dates=['Date'])

I want to get a list of the columns in this dataframe, their data types, and the number of non-null rows for each column. Which method should I call?

Answer: sales.info()

Explanation: The info() method will return a list of columns and other metadata, as shown here:

>>> sales.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 113036 entries, 0 to 113035
Data columns (total 18 columns):
 #   Column            Non-Null Count   Dtype
---  ------            --------------   -----
 0   Date              113036 non-null  datetime64[ns]
 1   Day               113036 non-null  int64
 2   Month             113036 non-null  object
 3   Year              113036 non-null  int64
 4   Customer_Age      113036 non-null  int64
 5   Age_Group         113036 non-null  object
 6   Customer_Gender   113036 non-null  object
 7   Country           113036 non-null  object
 8   State             113036 non-null  object
 9   Product_Category  113036 non-null  object
 10  Sub_Category      113036 non-null  object
 11  Product           113036 non-null  object
 12  Order_Quantity    113036 non-null  int64
 13  Unit_Cost         113036 non-null  int64
 14  Unit_Price        113036 non-null  int64
 15  Profit            113036 non-null  int64
 16  Cost              113036 non-null  int64
 17  Revenue           113036 non-null  int64
dtypes: datetime64[ns](1), int64(9), object(8)
memory usage: 15.5+ MB

Ref: info() - https://het.as.utexas.edu/HET/Software/Numpy/reference/generated/numpy.info.html

Discuss this question and answer on the forums

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 - Administration

AWS Database Migration Service causing problem - SQL Server as Source - Hello folks, I have a problem using the AWS Database Migration Service for implementing a transactional replication from SQL Server as a source database engine, a help is highly appreciated. The 'safeguardPolicy' connection attribute defaults to 'RELY_ON_SQL_SERVER_REPLICATION_AGENT'. The tools will start mimicking a transaction in the database for preventing the log to be reused and […]

Things need to check - Hi, can anyone please suggest what are things needs to check after windows patching and Sql Server Patching on the server. Eg : Services Running like..

SQL Server 2016 - Administration

Uptime history without Monitoring tool - I could able to view the last restart of sql server using dm_os_sys_info. But checking is there a way to see the history of uptime of sql server something like graph?My understanding is that DMV loses the information after the restart of the server. Also looking to see the CPU resources likes historical information . […]

Giving access to a couple of users to run specific SQL jobs - I have a requirement to grant a couple of users access to run specific SQL jobs that runs SSIS packages. Is there a way to grant them access in some way to address the issue of not granting them too much access.

Development - SQL Server 2014

SQL Data Flow Tool - Hi SSC, I'm looking for a tool which helps me achieve the following: Understand what happens inside a stored procedure and in which order Here's a partial example: SET @NoOfRecordsPendingBefore = ( SELECT COUNT(*) FROM RRawData (NOLOCK) WHERE R_Status_ID = @STATUS_REJECTED ) SET @NoOfRecordsInFile = ( SELECT COUNT(*) FROM RRawData (NOLOCK) WHERE R_Status_ID = @STATUS_NOT_PROCESSED […]

SQL 2012 - General

SQl Server Security - Dear All In my company using sqlserver Standard edition.As of now we are only maintain all the client database server client premises.but now company will change the business model.they bring partner base sales model.Here after we are not directly contact with clients. We will sell use base licence to Partner. then partner sell to client. […]

SQL Server 2019 - Administration

Create a new instance SQL server 2019 - Hi, I'm kinda new to setting up MS SQL DB related stuff. I want to create a new DB instance for SQL server 2019. There's already one instance running. I assume should go with the alternative called "Perform a new installation of SQL Server 2019"? Will this have any effect on the current running instance? […]

SQL 2008 Enterprise to 2019 ¿Enterprise or Standard? - Hello !! First, let me say im not a DBA expert, and im facing a hard decision We have a DB on SQL 2008 Enterprise. Since SQL 2008 is out of support from MS we decided to move to a newer version (2019) My main question is: Is there any performace difference between Standard and […]

SQL Server 2019 - Development

NVarchar field sizes - Hi, In my database I have a table with product information. The number of records in the table varies from 10.000 to max 50.000. There are approx. 20 text fields that are defined as NVarchar. There are no indexes on these fields. Activities on the table are in most cases less than 1000 times access […]

T-SQL: Pulling Different Data from the Same Table - Hi: The results of the first code below are attached as the file called "Results". Likewise, the results of the second and third code are attached as "Results2" and "Results3", respectively. The table for both of these codes is FMItem. The first code has a field called IM. This is equal to the FormulaID field […]

Reporting Services 2008/R2 Administration

Report Builder & RSReportDesigner.config - I installed SQL Server Report Builder 2008 2.0. When I try to connect to a SharePoint List, I get this error: The selected data extension SHAREPOINTLIST cannot be loaded. Verify that the selected data extension is correctly registered in RSReportDesigner.config, and is installed on the client for local reports and on the report server for […]

XML

How to get at value? - Hello, I want to pluck out the values for "" and "" but could not figure out the right syntax (don't know how to reference "<rd:"). Below is the XML code fragment, followed by the select statement. Any help is appreciated. thank you, beth - - - […]

Integration Services

API call with no wsdl - I have an API call that passes parameters via the query string and then returns either JSON or CSV. I know I can do it with a script transformation as a source but I wanted to use a web services as a source if possible? I have done some research but it seems that a […]

Articles Requested

Allow User to Run Jobs - A common question, but allow a normal (non sysadmin) to run a specific job. I'd like an article that shows 3 jobs in a system. Job 1 - User A can only run this job. User B can run this job Job 2 - User B can this job. Job 3- only the sysadmin can […]

COVID-19 Pandemic

Daily Coping 30 Apr 2020 - Take a minute to remember what really matters to you and why. My thoughts: http://voiceofthedba.com/2020/04/30/daily-coping-30-apr-2020/

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.