Archive for Questions Answered

Questions Answered From Presentation: SSRS 101 Creating Reports for Diagnostic Data

On Tuesday, July 7th, 2015, I had the privilege of speaking for the DBA Fundamentals Virtual Chapter. Thanks to the 375 who attended and to all who will be watching the recording. While I can’t read the comments during my presentation (way too distracting), I did enjoy reading the questions and comments that were sent to me. Below you will find answers to the questions I was sent.

Questions Answered

1. Are you talking about SSRS 2016 or 2014?

I was presenting using SSRS 2012, but the demos and discussions applied to versions 2008 through 2014.

2. When should you use SSRS RDL vs. RDLC (Remote Definition Language Client-side)?  Should you be able to invoke RDL from a web app (say web forms, asp.net) just as easily as RDLC?

The difference is RDLC reports will run on the client side. They don’t need to access the report server. RDL reports can be invoked on the client side, but they are rendered on the report server then delivered to the client. The RDLC reports would be part of an application and will take up client resources to generate.

3a. What is the difference between SSRS and Reports Application in Visual Studio 2010 (full version)?

3b.Why use SSDT over Visual Studio?

I liked both of these questions, and they have the same answer, so I wanted to group them together.

SSRS (SQL Server Reporting Services) files are generated in two applications, Report Builder and Visual Studio. Report Builder exists on the Reporting Server called Report Manager. Visual Studio is the Integrated development environment (IDE) that you write code in. Microsoft created (at least) two distributables for Visual Studio. The one that Application Developers, is only known as Visual Studio and the one that comes with SQL Server called SQL Server Data Tools (SSDT). The IDE is the same for both of them. When they co-exist on the same computer, they will be integrated. If you only have the SSDT version, then you can only create SSRS, SSIS, and SSAS projects. You can also download over project types for Visual Studio, such as PowerShell projects.

Note: For SQL Server 2008 it was called Business Intelligence Development Studio (BIDS). It was renamed to SSDT in version 2012.

3a. Is it better to use SSDT or Report Builder? I have been using Report Builder for my reports so far…

The answer to this depends on your comfort level for creating reports. Report Builder is meant for the power users in the business. SSDT is meant for developers. I’ve personally never used report builder, but that’s because I’ve been writing reports since before Report Builder was introduced. If you continue to create reports in Report Builder, then take a look into generating Report Parts. Those were introduced in SQL 2012 and are supposed to be “building blocks” to help create more complicated reports within Report Builder.

4. If you don’t have the source of the report can you get it back from the web

I am happy to tell you, yes! (and there was much rejoicing!)

In the Report Manager, pull down the menu to the right of the report and select download. You can also go into the properties of the report and see the download option in the toolbar for that report.

Answers201507_01

5. How did you link the two datasets?

The two datasets weren’t linked like you would see writing a JOIN statement. They were filtered using the same parameter. Datasets can’t be joined together within the report. There are a couple of functions introduced in SQL 2008 R2 that allow you to reference a single value from another data set, but that is as close as they have gotten.

6. How is the security configured within the data sources ? How can the double-hop authentication issue be avoided when accessing data from multiple servers ?

You have 4 different security options that you can use. Books online can tell you the details on each of them.

Answers201507_02

I personally use a SQL Server login, only used for reports to access the data. I then use windows authentication to access the reports themselves. Each data source can connect to a different server and even use a different SQL Server account.

I’m not sure I’m answering your question adequately since it can be read a couple of different ways. If you are looking for a solution to a double-hop authentication problem, then take a look at this article. I’ve faced this once, but it was about 5 years ago. The Double-Hop Authentication Problem

Send me an email if you still have questions on this topic.

7. I saw that you saved the password for the data source (during development). How can we ensure that it is encrypted when deployed to the server?

I would have to do some research to see how the password is sent during the publishing event, but I do know it is stored encrypted.  In fact, the Connection String, UserName, and Password are all encrypted. Go into the ReportServer database and run this query: SELECT * FROM ReportServer.dbo.DataSource.

If you are still concerned about the Data Source during the publishing event, then you can create the data source manually in Report Manager. As long as the SSRS project has the property “OverwriteDataSources” set to false, the data source will remain the one you manually created.

8. What if you want to show multiple databases (in your report)?

You can create as many data sources as you need and they can each point to a different database on a different SQL Server.

If you are gathering diagnostic data, like we were doing in the demo, then you might want to consider a two step approach. The first step would be to have an SSIS package or a PowerShell script retrieve the diagnostic data from each server on a schedule and save the data to a central database. Then you can have the report pull the diagnostic data from that central database.

9. Can hidden parameters have values passed to them, for example: can a hidden parameter list containing state names have “CA” passed to it when the report is opened?

Yes. That would be done in the parameter properties on the report in the Report Manager, from code, or from another report. You can also create Linked Reports in the Report Manager and change the values of the hidden parameters.

10. Is there any difference between previewing the report than just running it by r-clicking on the report?

Not really.

11. Can you set border style in your template? So it’s not there at all?

Yes. If you were to save a new tablix in the template with the borders set to None for each row. Then you would copy that tablix for each tablix you need. Unfortunately you can’t modify the actual tools in the toolbox.

12. Any strategy to fine tune multiple drop down option/filter in SSRS report.

Yes.

1. Make sure that the Queries that are populating the drop down lists are FAST.

2. If the drop down lists are filtering each other, known as cascading parameters, then have them filter within the report, instead of making a round trip back to SQL Server for the filtering. This would be done by applying the filter to the 2nd parameter in the filter property instead of attaching it to the parameter property.

3. Make sure that the predicates of each of the queries and the final data set have proper indexes.

4. If the queries that the drop down lists are using are really slow due to too many JOINs, then consider using an SSIS package to create a static table that is updated every X minutes/hours with the latest data. Then use that table for the parameter lists.

13. This is not the question you are looking for. Move along. Move along.

14. How to display a message in the report if the dataset doesn’t return any records ?

This is an excellent question and the answer is not used enough.

Set the NoRowsMessage property on the tablix (Table/Matrix/List) control. You can get to it through the property window. The value of this property is displayed when there is no data to show. The value can even be an expression.

15. How to get a big report to limit to 4000 records but well distributed in terms of days in a month?

I would love to have more information on why you have this requirement. It is an interesting one. Here is what you can do. Note: It will slow the query down though, so make sure you have good indexes on the predicates.

1. I have 1 million rows of random dates and numbers. This happens to be a very narrow table.

Answers201507_03

2. I use the ROW_NUMBER() function. It will give a sequential number to each row based on the partition. I partitioned the data based on Year, Month, Day. If your date field does not have time, then you can partition based on the date field. Within each partition, the data is sorted by Date then TestDataID (the PK). This will help guarantee the same order each time.

3. I determined how many days were in the range of dates I’m selecting from, divided 4000 by that number, and select only those row number values per date. This will provide an even distribution across days.

4. Since you can’t guarantee that the number of days will divide evenly into 4000, you need to either have less than 4000 rows returned, or more than 4000 returned by adding 1 to the number created in step 3.

Answers201507_04


–1 million rows
DECLARE @StartDate AS DATE = ‘1/1/1972’;
DECLARE @EndDate AS DATE = ‘1/1/1973’;

SELECT DATEDIFF(DAY,@StartDate, @EndDate) AS NumberOfDays;
WITH cte_ranking
AS
(
SELECT TOP 100 PERCENT
TestDataID
,SomeDate
,SomeTextNumber AS SomeNumber
,ROW_NUMBER() OVER (PARTITION BY dateyear, datemonth, dateday ORDER BY datevalue, TestDataID) AS RN
FROM
DemoProgramming.dbo.TestData AS td
JOIN dbo.DimDate AS dd ON td.SomeDate = dd.DateValue
WHERE
td.SomeDate >= @StartDate
AND td.SomeDate <= @EndDate
ORDER BY datevalue
)
SELECT
cte.TestDataID
,cte.SomeDate
,cte.SomeNumber
FROM
cte_ranking AS cte
WHERE
cte.RN <= (4000/DATEDIFF(DAY,@StartDate, @EndDate)) + 1;

–Returned 4038 rows

[\SQL]

16. With subscriptions is it possible to make one subscription to use the current fiscal month to generate report vs. creating 12 subscriptions for each fiscal month?

Unfortunately no. You can do it based on calendar months, but not based on fiscal months where the first day of the fiscal month may not be the 1st day of the calendar month. I would love to see this feature.  If this is a must have requirement, then email me and we can talk about some “creative” solutions.

17. What are the different security roles on report server side?

The SSRS development team were kind enough to add the definitions on the security screen.

Answers201507_05

 

18. Can you append the date/time to the file name that gets created with a subscription that saves to a fileshare?

Yes. Add the @ExecutionTime parameter to the filename.

19. How do you set-up the email option for the subscription?

First your Report Manager needs to be setup to send email. I usually have my Sys Admin help me with that.

After that, it’s a matter of populating fields (and there was much rejoicing.)

If you use the standard subscription, then the setup screen will look like figure A below. If you are creating a data driven subscription, then the setup screen will look like figure B below. Either way, you need to set all the properties. Then select a schedule for the emails to go out on.

I would highly suggest using Active Directory groups for the To list. This make is easier to manage when people change jobs. You may also want the email to go to yourself for a few days or weeks to make sure it is going out as predicted.

figure A

Answers201507_06

figure B

Answers201507_07

 

Thanks for all the fish

I wanted to give a shout out to Glenn Berry for letting me use his diagnostic queries for my demos. You can find the full diagnostic script, per SQL Version here on Glenn’s website.

The downloads for this presentation are available on the DBA Fundamentals Meeting Archives page and will be available on my website under Resources shortly.

Questions Answered From Presentation: Changing Your Habits to Improve the Performance of Your T-SQL

QuestionMark_127880048This past Tuesday, Oct 8th, 2014, I had the privilege of speaking for the DBA Fundamentals Virtual PASS Chapter. It was my biggest audience to date and the fourth biggest audience for the user group to date. There were 374 people listening in, and they weren’t all from the US. I was thrilled to see that I had at least one person listening in from the UK.

Since there were so many people on line, I couldn’t answer all the question…thus my post today. You can download the slide deck, demos, and my sample database. (All my presentations eventually find there way to my resources page, here.)

I will post the link to the presentation on this page when it becomes available.

Questions Answered

1.    What is the name of the Red Gate tool for source control you mentioned?

Red Gate’s tool is called SQL Source Control. It’s like a bridge between your source control repository and SSMS or Visual Studio. They have a trial version that you can download here

2.    What to do when you come into a team and there was no SQL coding standards before and we do not want to spend time re-writing everything?

I would first get buy in on why standards are important to have and outline the standards you want to use. Then I would slowly change the code. What I mean by this is, update the code as you make changes to it. For instance, you need to modify stored procedure XYZ, I would add the standards to only that stored procedure or even only the part of the stored procedure that you change.

3.    What version of SSMS are you using?

In the presentation I was using SQL 2012, but all the concepts I covered applied to SQL 2005, 2008 and 2008 R2. Even the Template Browser and Template Parameters I covered are available in those versions (Talk about a well kept secret!). Note: If you need the sample database in SQL 2008 or SQL 2008R2, please let me know and I’ll see what I can do.

4.    Can you give an example of commenting within a stored procedure?

Sure. You can comment using two different ways. You can comment at the end of a line using two dashes. You can also comment any where in the code using /* and */ to encompass the comment. This allows for comments to spend multiple lines.

When I’m adding comments about my changes, I will include the date, my name and brief comment. If I’m lucky to have a work item or bug tracker application to keep track of my work, then I’ll reference that number as well. Here is an example.


USE AWMonkey
GO
/* ***********************************************************************************
Purpose:    Returns list of Territory Groups.
Notes:
Sample:
EXEC List.GetTerritory
Author:     THEZOO\mstuewe
Date:       9/16/2014

Revision History
(Change Date)    (Author)        (Description of Change)
-----------------------------------------------------------------
10/10/2014        Mickey Stuewe    #2345 Added All to the list of values as requested
By end user.
************************************************************************************ */
ALTER PROCEDURE List.GetTerritory
AS
BEGIN TRY

SET NOCOUNT ON;

SELECT
st.TerritoryID
,[Group] AS CountryRegion
,st.Name AS Territory
FROM
AdventureWorks2008R2.Sales.SalesTerritory AS st
UNION ALL
SELECT
-1
,' All'
,' All'
ORDER BY
CountryRegion; --Added the ALL to the list.
END TRY
BEGIN CATCH

DECLARE
@ErrorMessage AS nvarchar(3000)
,@ErrorSeverity AS int;

SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.'

+ OBJECT_NAME(@@PROCID, DB_ID()), N'SQL Object Name Not Available')

+ N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' + CONVERT(nvarchar(5), ERROR_LINE()) + N' - ' + ERROR_MESSAGE();

SET @ErrorSeverity = ERROR_SEVERITY();
RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

SET NOCOUNT OFF;

5.    Is there any configuration for the Template Browser? Can it integrate with any source control?

No configuration for the Template Browser is needed. Here are the generic steps to integrate your Template Browser folder with your source control repository. Each Source Control repository will be different, but these are the general steps.

1. Open the location of the Template Browser. Since I’m using SQL 2012, I found mine here:

C:\Users\<Your User Name>\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql\<My Templates>

2. Import your templates into your source control repository.

3. Set your templates folder as the “working folder” location.

4. Test by getting the latest from your repository.

5. Connect the template browsers on your team’s computers the same way.

6. Let them know when you make changes to the templates so that they can do a “get latest” to download the changes to there computers.

6.    Is there any kind of Freeware repository tool?

Yes, Subversion is an open source product and widely used repository tool. It has two parts. The first part is the repository which resides in a common location, usually on the network so that everyone can use it. Then each user needs to have a subversion client tool to connect to the repository. There are many different clients to pick from. I use Tortoise. Why? It was suggested to me and recommended by Red Gate. Since I was going to use SQL Source Control by Red Gate to connect to subversion, I decided to stick with there recommendation.

7.    So, Row_Number() is non-deterministic?

Yes, that is correct.

8.    Is there any difference or improvements in using a CAST vs CONVERT or vice versa?

There isn’t a different in using the two functions, in terms of performance. As Jeff Joy kindly pointed out, the difference between the two comes down to compliance.

Jeff Joy – CAST is ANSI – compliant while CONVERT is Microsoft-specific casting function that builds in additional formatting functionality. If you may ever need to convert to another platform you need to write code that is cross-platform compliant, use CAST.

The downside of writing all of your code to ANSI compliant standards, is you miss out on quite a few rich features that Microsoft has added to the SQL language. These rich features will help SQL statements perform faster or make your life easier. 

9.    Isn’t CTEs much better than subqueries? This is in regards to readability?

Yes CTEs (Common Table Expressions) are much easier to read. There are even patterns that can be accomplished with CTEs that can’t be accomplished with sub queries, but CTEs aren’t always faster. I have had a few queries I’ve had to re-write with sub queries because I needed every millisecond of performance.

10.    Can I see the trace options selected?

Yes, here are the events I captured. I also isolated the database, to the demo database.

 

QuestionsAnswered20141010_Image1

11.    Is there a standards document about T-SQL Formatting?

I don’t have one I can give you. I can create a template for you if you would like. Just send me an email.

12.    Can you e-mail that URL to us?

I’m not sure which URLs this is referring to, so I’ve listed the URLs that were mentioned in my presentation.

13.    Is there better performance between CTE and Apply?

Comparing CTE and APPLY is like comparing apples and oranges. They are two different things. A CTE provides you the ability to create a query to be used within another query. APPLY is an operator like INNER JOIN, but it is specifically used to join table functions (in line and multi line) as well as sub queries and CTEs.

14.    On the comparison of temporary, table variables, and materialized tables…Why was the second execution plan eligible for auto-parameterization and the others were not?

This will take a little bit of investigation since I don’t know off the top of my head. I will post this one in the next week or so.

15.    What is the proper way to use DISTINCT with lots of columns in the SELECT without duplicate records? (Per our SDLC, avoiding using DISTINCT with lots of columns.)

The DISTINCT operator causes a SORT in the execution plan which is usually a very costly operator. Unfortunately sometimes you can’t avoid it. One thing that you can do, is see if there are other ways to filter the data so that there aren’t duplicate records. For example maybe there is a column that marks the latest record as active. You could filter on that field to provide uniqueness across the rest of the fields. Another example would be joining to another table that will help reduce the records, but not alter the data you need to return.

IMHO, your SDLC is trying to avoid unnecessary DISTINCT operators in the code. I had added that same requirements to my SDLC document when DISTINCT was being abused. I later added the comment, “If you can prove the need for the DISTINCT operator, then the use of DISINCT will be approved.”

16.    About predicate and especially LIKE, we know that to put predicate more efficient, we shouldn’t start with a wildcard, but if we need to return data that end by defining string, it exists a way to improve that?

See # 17 below.

17.    REVERSE string?

I wanted to answer 16 and 17 together. #18 is related, but I answered it separately. While all the string functions are deterministic, the particular pattern that needs to be implemented (looking at the end of string), does not allow it’s self to an efficient execution plan. This has to do with how indexes are built. The keys in the index are stored sorted. This means that finding records can only be efficient when looking from left to right, not right to left when comparing strings. You can see this by running the following sample on my demo database I provided.

I created an index on the field we are looking at, then I tried various techniques to get the last three characters. All three execution plans show an Index Scan.


USE DemoProgramming
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.TestData') AND name = N'IX_TestData_SomeDateText')
DROP INDEX IX_TestData_SomeDateText ON dbo.TestData WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX IX_TestData_SomeDateText ON dbo.TestData
(
SomeDateText ASC
)

SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
td.SomeDateText LIKE '%-09'

SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
REVERSE(SomeDateText) LIKE '90-%'

SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
LEFT(REVERSE(SomeDateText),3) = '90-'

When you run this sample, you do get an Index seek.


SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
SomeDateText LIKE '2004%'

With all that said, if a particular column is constantly being split into separate parts to be searched, then I would consider storing those separate parts in fields in the database. That way they can be indexed and improve performance.

18.    Full Text index vs Like?

I have not had the opportunity to use or even investigate Full Text indexes, so I can’t comment on this at this time. (sorry)

19.    Is it ok to manually create tables in the temp DB? Would that be the same as creating materialized tables, but will be wiped out after a DB server restart?

Temp tables and global temp tables are tables with in the TempDB. As far as creating materialized or permanent tables within in TempDB, that is not a good idea. TempDB can already be burdened by all the other queries that use temp tables, table variables, and worktables. It would be better to create materialized tables outside of the TempDB.

20.    For the Template explorer…how do you share your templates to other users in SQL because when we create templates they seem to only be available to the user specifically that created them?

See Question #1.

21.    What’s the best hosting solution for MSSQL server standard/enterprise?

This is outside the scope of this session and an area I don’t have a lot of experience with.

22.    Does including Actual Execution plan also puts an overhead on the server?

No. The Actual Execution plan is what is created when a query is ran. The query itself can cause overhead, but not the plan. In fact, the engine allows a specific amount of time to find the best plan. If it can’t go through all the different possibilities, then it will do the best it can with the possibilities it ran through . You can see this in the execution plan. Click on the SELECT icon and look at the properties. For this query, it told me it found a “good enough plan”.

 

QuestionsAnswered20141010_Image2

 

If you want to get the last execution plan that was generated for a query, then you can get it using sys.dm_exec_query_plan. See how to use this DMV here.

Last but not least…

23.    What’s your favorite whisky?

Well in the immortal words of Grant Fritchey…that depends. It depends on where I am, what I’m eating, and how many people I’m with. (I know, that is a weird one. But I find I can handle the smokier Scotchs when I’m around a group a friends, but not when I’m home relaxing.) Also, I drink all my whiskey neat.

So, since I don’t have one favorite, I’ll answer the question by country in the order of my preference.

  • Ireland – I don’t think I’ve met an Irish whisky I didn’t like, but my favorite (right now) is Redbreast.
  • USA – I love 100% Rye whiskey! Mitcher’s Rye and Highwest Rendevous Rye are at the top of my list.
  • Scotland – I like the Fruity and Spicy Scotch’s from Speyside, with Oban being at the top of list.
But wait there is more

Mark Finch mentioned a free formatting tool. I wanted to include it in my summary since I had never heard of it. You can find it at poorsql.com.

Thanks for all the fish

I want to thank everyone for attending my presentation, as well as for all the encouraging feed back I received.

%d bloggers like this: