It’s coming…the next version of SQL Server. What surprises are in store for you? “In-memory” is a hot buzzword. Windows Azure capabilities are expanding. Data Explorer is being talked about. Jes will give you an idea of some of the new features and enhancements coming next!
Get an overview of new backup capabilities, integration with Windows Azure, new capabilities in Resource Governor, incremental statistics, and an overview of In-Memory OLTP (the artist formerly known as Hekaton)!
One question came up several times: which features will be Enterprise Edition only, and which will be available for all versions? As of today, Microsoft hasn’t released that information – but keep an eye out for “Features Supported by the Editions of SQL Server 2014″ on msdn.microsoft.com.
It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O.
From stage right, an intrepid audience member raised his hand and asked, “Can you think of an example of when you would use filtered indexes instead of partitioning?”
This question left me speechless (temporarily).
My first thought was one of requirements and practicality: “What if you have Standard Edition? You can’t use table partitioning, but you could create an indexed view” I said.
The better question would have been, “What are you trying to accomplish?”
On the surface, filtered indexes and partitioning may appear similar: both are designed to hold only portions of a table’s data in separate structures. However, how they go about doing this, the use cases for each, and the requirements for each are very different.
Filtered indexes store only a portion of the data in a table. For example, in an orders table, instead of storing all orders in a nonclustered index on the “quantity” field, we could create a filtered index to only store orders with a quantity greater than or equal to 20, or between 10 and 19. In this case, the only column stored in the index leaf pages is the quantity (and the clustered index key).
Sample of data from the table
What a nonclustered index on Quantity would store
What a nonclustered index on quantity, filtered to include quantity between 10 and 19
Let’s say the data in the underlying table changes – someone wants to increase their order quantity from 750 to 1,250. The row is updated at the data level – the clustered index. At the same time, the nonclustered index must also be updated.
Table partitioning breaks the whole of the data into separate physical structure based on a partitioning key. It’s generally used to improve query performance or make administration of large tables easier. Partitioning is based on a schema and a function. The table contains a partitioning key – the field in the table that contains the value you’ll split your partitions on. Most commonly, this is a date field of one sort or another – tables are partitioned by day, month, or year.
To directly compare this to a filtered index: could you use an order quantity as a partitioning key? Yes, as long as it’s a valid data type. The difference is that the partition is going to store all columns from the table – not just the key. When you insert or update a row, SQL Server would have to look at that value to determine which partition to place it on. It would then perform the update on the appropriate partition.
A table, partitioned on quantity – 1-10, 11-20, 21-30, etc
Want to know more about partitioning? Start here.
Comparing How They’re Used
When it comes to reading the data, if you have a filtered index for a specific value, and the query optimizer can use that index, you can often reduce I/O by orders of magnitude because you are storing less of the data in the index itself. With partitioning, SQL Server has to determine which partition the data is stored on, then access it. This can be helpful on very, very large tables – but the care and time taken to implement it and the upkeep required mean it must be very carefully considered and maintained.
Which Should I Choose?
There are many features in SQL Server that may appear similar, but once you look at how they work and what they do, they are very different. Database mirroring is not the same as replication. Change Data Capture is not the same as Change Tracking. Filtered indexes and partitioning solve two very different problems. When considering implementing any new feature, stop to ask, “What is the problem I am trying to solve?”
Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation.
- Security: we create views so that a user can read specific columns out of certain tables, but not all the data.
- Simplification: sometimes, it’s easier to write a SELECT against one view than a SELECT against multiple tables, with joins – repeatedly.
- Aggregation: again, it can be easier to SELECT already-aggregated data than to write the query – repeatedly.
The down side to views is that when you query them, you’re still reading data from all of the underlying tables. This can result in large amounts of I/O, depending on the number and size of tables. However, you can create a unique clustered index on the view – referred to as an indexed view – to persist the data on disk. This index can then be used for reads, reducing the amount of I/O.
There are some limitations to indexed views, but when you can create one and it improves performance, it really improves performance. But, as with all features in SQL Server, indexed views aren’t the answer to everything. Here’s a quick look at some things you can and can’t do with them.
The view definition can reference one or more tables in the same database.
Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.
The view definition can’t reference other views, or tables in other databases.
It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.
Choose the Right Tool
If you’re looking to have a complicated, aggregated query persisted to disk to reduce I/O, an indexed view may be the right tool for your job. Test it as an option, and if it works, put it to use!
Learn more about indexed views in my video The Okapis of SQL Server Indexes!
When you write a query, you (usually) don’t want to return all the rows from the table(s) involved – so you add a WHERE clause to the statement. This ensures that fewer rows are returned to the client – but doesn’t reduce the amount of I/O done to get the results.
When you create a nonclustered index, you can add a WHERE clause to reduce the number of rows that are stored at the leaf level – a filtered index. By having fewer rows in an index, less I/O is done when that index is used.
Filtered indexes are great performance boosts if you have a value that is used in a predicate very frequently, but that value is only a small amount of the total values for that table. (Say that ten times, fast.) For example: I have an orders table that contains a Status column. Valid statuses are Open, Processing, Packing, Shipping, Invoicing, Disputed, and Closed. When the business first starts using this table, there’s a good chance there is a fairly even distribution of orders across these statuses. However, over time, a majority of orders should be in Closed status – but the business wants to query for Open, or Disputed, which are only a small percentage.
This is where a filtered index can come in.
When you add a WHERE clause to the index creation statement, you’re limiting which rows are stored in the index. The index is smaller in size and more targeted. It can be trial and error to get the query optimizer to use the filtered index, but when you do, gains can be significant. In one case study I have, logical reads dropped from 88 to 4 – a 95% improvement! What are some of the things you can – and can’t – do with them?
Use equality or inequality operators, such as =, >=, <, and more in the WHERE clause.
Use IN to create an index for a range of values.
Create multiple filtered indexes on one column. In my order status example, I could have an index WHERE Status = ‘Open’, and I could have another index WHERE Status = ‘Shipping’.
You can have multiple items in the same where clause. I could have an index WHERE Status = ‘Open’ OR Status = ‘Shipping’.
Create a filtered index for all NOT NULL values – or all NULL values.
Create filtered indexes in SQL Server 2005.
Use certain expressions, such as BETWEEN, NOT IN, or a CASE statement.
Use date functions such as DATEADD for a rolling date range – the value in WHERE clause must be exact.
The query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL. This is one of the hardest limitations to work around – so I usually reference Tim Chapman’s The Pains of Filtered Indexes for help with this!
Filter All The Things!
Using either a WHERE clause in a query to limit the rows returned or a WHERE clause in a filter to reduce the rows stored is beneficial to performance. You want to reduce the amount of data read and the amount of data returned to clients to improve performance. Filtered indexes can help!
Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you’ll get. This blog isn’t about the magic of the buffer pool or the plan cache, but you should understand how important they are to your server’s performance. Since memory is such an important resource, you want to know how much of it you’re using at any time.
How can you track your server’s memory usage? One way is to use the Performance Monitor (Perfmon) counters exposed through the sys.dm_os_performance_counters DMV. One indicator of memory performance is Page Life Expectancy (PLE). You can capture basic memory usage over time by setting up a SQL Server Agent job to query this DMV, inserting the results into a table, and reporting on the table results.
I’ll show you how to collect this data and report on it!
Collecting the Data
I have a “DBAInfo” database on my instance that I use to track metrics and other information. I create a new table, MemoryHistory.
USE DBAInfo; CREATE TABLE MemoryHistory (ID INT IDENTITY NOT NULL, CollectionDateTime DATETIME, PerfmonObjectName NCHAR(128), CounterName NCHAR(128), CounterValue BIGINT)
Then, I create a new SQL Server Agent job that runs every 5 minutes.
The only step in this job is the below query, which queries the DMV and inserts the results into the table I created.
INSERT INTO MemoryHistory SELECT CURRENT_TIMESTAMP, object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Buffer Manager';
I schedule the job to run every five minutes.
Viewing The Data
Now, this data isn’t going to do me any good unless I view it, and make a decision or perform an action based on what I learn.
To view the data I’ve collected, I run the following query:
SELECT ID, CollectionDateTime, CounterName, CounterValue FROM MemoryHistory;
That’s a lot of junk to sort through when all I want to see is PLE, so I narrow down the query a bit.
SELECT ID, CollectionDateTime, CounterName, CounterValue FROM MemoryHistory WHERE CounterName = 'Page life expectancy';
But who wants to read through results like that each time there’s a problem to see when PLE rose or fell? Not me. I’d rather see it in a graphical format. How can I do that?
SQL Server Reporting Services
I have SSRS at my disposal. I’m going to create a very simple report that will allow me to enter start and end dates, and will display a line chart for PLE during that time.
Reporting on the Data
I set up my report to have DBAInfo as my data source. In order to choose dates, I use the following query as my dataset.
SELECT ID, CollectionDateTime, CounterName, CounterValue FROM MemoryHistory WHERE CounterName = 'Page life expectancy' AND CONVERT(DATE, CollectionDateTime) >= @Start AND CONVERT(DATE, CollectionDateTime) <= @End;
I change my @Start and @End parameters to “Date/Time” so I get a date picker.
I drag a Line Chart onto the design surface and add the CounterValue as my Value and CollectionDateTime as my Category Group.
I can preview the report to view it:
Last but not least, I’ll deploy this report to Report Manager so that I and others can run it, or even schedule a regular subscription.
There are several ways to improve this report. How can you modify the query to capture date and time data individually? How do you add parameters to the report so the user running it can choose their own date range? How would you collect and display data for different instances?
Want to know more about how to use Reporting Services to create reports for your environment? Check out my 90-minute SQL Server Reporting Services Basics training video!
Let me ask you a question: do you want to boss the SQL Server query optimizer around?
If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible.
If you answered yes: you’re a brave person.
Maybe you’ve hit upon the perfect index for a specific query, but for some reason the optimizer won’t use it. But you know it improves performance. How can you make the query optimizer listen to you?
Index hints are a powerful, yet potentially dangerous, feature of SQL Server.
Let’s look at an example. I’m working with AdventureWorks2012. The database has two related tables, HumanResources.Employee and Person.Person. They are related through the BusinessEntityID column. I want to retrieve information about the users and their logins.
USE AdventureWorks2012; GO SELECT PER.FirstName, PER.LastName, EMP.LoginID FROM HumanResources.Employee EMP INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID;
Let’s look at the execution plan.
A nonclustered index seek is performed on Employee.AK_Employee_LoginID, a nonclustered index on the LoginID column. A clustered index seek is performed on Person. Note the cost is 0.217439.
I notice that the Employee table has another index, PK_Employee_BusinessEntityID, which is on the BusinessEntityID column. I want to force my query to use this index instead. I can do this by using the WITH (INDEX) hint.
SELECT PER.FirstName, PER.LastName, EMP.LoginID FROM HumanResources.Employee EMP WITH (INDEX (PK_Employee_BusinessEntityID)) INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID;
Let’s look at this execution plan.
Now, a clustered index scan is performed on Employee. Note, though, that the query cost has increased – to 0.220402.
In your case, the index you force the query to use might get the better cost – for now. But what happens when more data is added to the table, and statistics change? What happens if you update SQL Server, and the query optimizer changes?
Eventually, the index you’re using may not be the best one for the job – but SQL Server is going to continue to use it anyway. You’ve told it to do so, and it’s doing to keep doing it.
Think about how you would get rid of it. Is there an easy way to search all of your code – application code, stored procedures, report definitions – for this specific index hint, to remove it?
Another point to consider is what would happen to that code if the index was disabled or deleted? Would it continue to run? Let’s give it a try. I issue a disable index command.
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee DISABLE;
Then I run the same query as before, and what happens? I get an error:
Msg 315, Level 16, State 1, Line 1
Index “PK_Employee_BusinessEntityID” on table “HumanResources.Employee” (specified in the FROM clause) is disabled or resides in a filegroup which is not online.
Index hints can be a powerful feature, but use with caution. Look for other ways to optimize your query – perhaps a different index can be created, or your query can be rewritten. If you can’t find a better way, document the use of the index hint and its purpose. When you upgrade SQL Server, test whether that index is as effective.
Remember, the query optimizer does its job really well – let it.
Welcome to the second day of #SQLPASS #Summit13! I’ve been having a blast this week – presenting a precon with Brent and Kendra, watching great speakers like Erin Stellato and Bob Ward, chatting at the Community Zone, and walking the vendor booths.
Today is one of my favorite events – the annual Women In Technology Luncheon! Our topic is Beyond Stereotypes: Equality, Gender Neutrality, and Valuing Team Diversity. We have a great panel – Erin Stellato, Rob Farley, Cindy Gross, Kevin Kline, and Gail Shaw. This is an incredibly diverse and brilliant group!
12:10 pm – Tom LaRock is kicking off the event! He introduces our moderator, Mickey Stuewe. She reminds us to ask questions and follow along on Twitter using the #passwit hashtag.
12:12 – Today we’re talking about diversity – making sure everyone is included. Mickey introduces the panelists! Cindy Gross is an MCM and member of AzureCAT. Rob Farley is a business owner and MCM and MVP, and outgoing PASS board member. Kevin Kline, pass president of PASS, MVP, and author – and father of several daughters! Erin Stellato is an MVP and brilliant consultant. Gail Shaw is an MCM and MVP, and contributes to the community in many ways.
12:14 – Our first question is about fitting in. Gail tells us she doesn’t always fit in – not because she’s a woman, but because she’s a geek. She spends her weekends playing D&D! (I played many years ago. I loved it. I want to do it again!)
12:15 – Have you experienced subtle cultural differences that make it hard to fit in? Rob says he does see people who exclude others because they are different. But that’s not him. In any environment where there is a large number of the same people, it can happen that there are assumptions. Let’s not forget about religious differences as a form of diversity and exclusion as well – important to remember at a large, international event like this too! Remember to tell people how you value them.
12:19 – Cindy, how can you tell when you’re being treated differently, and why? She’ll go to a coworker or friend and ask for advice. She has a group of people she can go to for a second opinion. “When I react to something, would I react that way if it came from someone else?” is what she often asks.
Kevin: There’s a lot of interesting scientific research going on right now. The average person can know about 150 people really well. Beyond that, stereotypes can save time. “They’re like the index pages in a database” gets a good laugh. He talks about the difference between introverts and extroverts. Introverts will internalize and be introspective about comments made towards them. Follow Cindy’s advice – talk to others about a situation or comment that was made and get their advice about it.
12:26 – Kevin, how do you explain that you think you’ve been treated differently to your boss? Database professionals will spend a lot of time debugging code and resolving problems, but we don’t spend enough time figuring out the people we work with. Why do they get up in the morning? What drives them? Talk to your boss. If you ask them if you made a molehill into a mountain, and he or she says yes, you did – think of it in one of two ways. They could have a very different set of values from you. Or, we may not have properly expressed how this behavior affects our values. You need to have your own “values statement” – and share that with your boss, and even coworkers. (What I hear is: we need to realize that although our job is technology, we work with PEOPLE. You have to be willing to understand and talk to PEOPLE.)
12:33 – Rob, how do you deal with being on a team when you’re excluded? If you see someone being excluded, you have to speak up. If you see a situation that is wrong and you don’t say something about it, it doesn’t help. You’ll be wracked by guilt. Stand up and be the person who champions what is right.
Gail says that if the person you call friends are the ones that say you need to change, you might need to redefine who your friends are. She recently had to “break up” with a friend because she wanted Gail to be something she wasn’t. She wasn’t accepting. Sometimes it’s best to burn those bridges. Find friends that are interested in who YOU are and who listen to your concerns.
12:40 – Cindy says there is so much more than just gender that we need to take into consideration. It could be so many things – your personality, what you support politically or socially. We all have our own stereotypes, too.
Gail says that she has many male, geek friends. They feel excluded because they would rather stay in and play a card game instead of going to a football game. It’s not just women who feel this.
12:45 – Erin, how do you establish and build relationships with coworkers in a diverse setting? You have to look at how you build any relationship – you build a common ground. You share interests and beliefs. Find common ground with your coworkers – we can all find something. You have to grow and celebrate that. Use the common ground to make your team better. “You can lead without being the leader.” Reach out to every member of your team – even if a “boss” isn’t doing so. Any good relationship takes time – and that’s OK, you just need to accept that. Find people’s strengths and celebrate the diversity.
Kevin: Even if you don’t have an opinion on a topic, say that. Don’t say nothing. People take silence as you don’t care, or you agree with what is going on. Introverts will often be quiet, and let extroverts run the show – even when it’s, “Where do we go for lunch?”
12:50 – What techniques have you seen to make events more inclusive in a professional setting? Kevin says when PASS was being founded, they didn’t have the resources other groups did – like money. They had to be better at people. “The antidote to swagger is humility.”
12:55 – Best part of the day! Questions from the audience!
Question from online – how do we celebrate conclusions vs. milestones? Rob says we need to recognize that different people celebrate different things. Even here this week, some people come to Summit to see friends. Some come to work on their career. Some come for specific sessions. We are all different. Recognize that we all need to celebrate – support others in the way they choose to do so.
“How do we foster compassion in the workplace? There’s a feeling that compassion is the antithesis of a successful team.” Kevin – there’s a ton of research in how to make teams work faster, but what about “how do we make this team higher quality?” Someone has to start by demonstrating it – one person. You have to get the people with influence and authority to model those behaviors. Erin reminds us, “You can lead even if you aren’t the leader.” And if it bothers you that much, sometimes, you may be at the wrong company. If you can’t affect change, you may need to leave.
“What is the best way to disarm someone who is applying a stereotype to you?” Gail says, there is something in common between you. Be polite. Find that something in common and work from there. You’ll never get someone to stop applying a stereotype by bashing your head against it. Cindy adds that if you try to take it head-on and convince that person that YOU aren’t that stereotype, you’re not solving that problem. The stereotype will still be in that person’s head. Rob says, on the inside, have pity for them. They are missing out on who you actually are. Care for them. Try to get to know them for who they are.
“I was walking around the expo yesterday and had a conversation. You’re a woman, in technology, you’re a foreigner, you’re an athlete – how are you going to succeed in this industry? How would have you answered?” Erin says, “I would have said, “How am I NOT going to succeed?”" Gail would have said, “Who are you to tell me I’m not going to succeed?” Cindy had a professor call her into his office right before graduation. He commented on her hair color and told her she wouldn’t succeed in the real world. Now look at her! What other people think is irrelevant.
1:12 – We could talk about this all day, but what is our call to action?
Gail – take a look in the mirror and make sure you’re not the one doing it. Don’t be the problem.
Rob – love people back regardless of how they are treating you. Don’t be part of the stereotype.
Wow, this was inspiring! Thank you to our panelists for being on stage and sharing your experiences. Thank you to those who stepped up and asked questions. Thank you to the audience, both in person and online. I look forward to next year’s event!
Problem: your database server’s performance stinks like Limburger cheese.
Solution: find the queries that are making that smell.
Hiding in Plain Sight
Your database server knows exactly which queries are performing the worst. When queries are executed, the execution plans – and associated execution statistics – are stored in memory (with a few exceptions). This information is stored in the plan cache, and you can access it by querying a couple of DMVs.
One way you can get this information is to run this query in SSMS. But what if you wanted it on a regular schedule, like every Monday morning, so you knew what queries hit your server the hardest in the last week? What if you wanted developers to have access to this information, without letting them run wild in SSMS?
Write a report!
Building a Basic Table Report
The most basic way to display information in SQL Server Reporting Services (SSRS) is by using a table. I’m going to go into Report Designer, add a new data source to connect to my instance, use a slightly modified version of the above query to return the top 10 queries by total CPU, and add a table to the report.
Not sure how to do all of that? My 90-minute SQL Server Reporting Services Basics training will get you up to speed!
Here’s my design:
And here’s a preview of it:
It’s functional, but it could be better. Some of the things I’d like to see in this report:
- The time the service started. The plan cache is only good from the time of the last restart (or the last time the plan cache was cleared, but I hope you’re not doing that on a regular basis).
- Formatting. Some of the numbers are hard to read, and it’s ugly. No one wants to look at ugly data.
- Measurements. Is that CPU in milliseconds? How about the duration?
- Can I get a link to the execution plan?
Improving the Report
I’m going to add a header to the report to display the last restart date. I add a new dataset which queries sys.dm_os_sys_info for the last start time. I add a header to the report, and add a textbox. I build an expression to display the last start time.
The report looks sharper already!
To display the rest of the information, I’m going to use the list control – I think it’s one of the most under-used features of SSRS. A list lets you add multiple report elements – text boxes, tables, images – to one space and arrange them as you wish. The list starts with two textboxes, one for the number of executions and one for the last execution date. Since I’m combining text and data, I use expressions.
Then I add a table to display the CPU, reads, and duration. I make sure I add appropriate labels for the measurements. I also format each of the results as a number so the numbers have commas in them.
Then, I add another textbox, display the query text, and format it with a fixed-width font so it’s easier to read.
Here’s the design:
Here is what the report looks like when run:
That’s a lot easier to read!
I’ve knocked everything off my list except the execution plan. I’m going to add another text box and add the plan.
That is not readable. Here’s the issue: the plan is stored as XML, and SSRS can’t natively display XML. I could leave this here for a user to copy and paste into SSMS or SQL Sentry Plan Explorer, but that’s a lot of work. I decided to take it out of the final version of my report.
The last step is to deploy this to Report Manager. After I deploy it, users that have permissions to the folder it’s deployed to can view it – without having to go into SSMS or coming to me for the information. They can also subscribe to it to receive a copy on a regular basis.
The next step to be taken: tune those queries!
SSRS can be used to solve a variety of issues – it isn’t solely the realm of analysts that need to know the sales from last quarter or what products are the most profitable. As a DBA or developer, you can learn to use SSRS and use it for multiple tasks. Any time you need to display data, consider SSRS as a tool!
I could be talking about the tables in my database – I do make them skinny by using the right data types – but in this case I’m talking about SQL Server Reporting Services tables. You’ve seen them before. The average table is bland and boring, with nothing to distinguish it from the next, and no reason for you to remember the data in it.
Is this memorable?
Being the Tim Gunn of SSRS, I’m going to transform this table so it looks good.
The default formatting isn’t easy to read. For example: for Order Date, I only want to see the date; and I want the sales columns to show dollar signs and decimal points. All of this can be done using the Format property of a cell. (You can find formatting options here.)
I set my OrderDate text box to “d” for short date, and my Subtotal, Tax Amt, Freight, and Total Due text boxes to “c” for currency.
I do other minor cleanup as well – I set the font of the report title to bold, I set the font of the column headers to bold, and I adjust the width of some of the columns.
The report is looking better already.
The order of the records isn’t the way I want to see it. We’re looking at the information sorted by order ID. The purpose of this report is to see, at a glance, which sales territory and which sales person have the highest sales for a given date range. I need to add groups for territory and salesperson.
Groups can be added at the row and column level, by using the grouping pane. I add two groups to this table – one based on sales territory ID, and the other based on sales person ID.
I add totals at the group levels and preview the report again. It’s much easier to read now.
If I exported this report to PDF, it would be 27 pages. No one is going to read through all of that to try to figure out which territory had the most sales. I need to either reduce the information in the report, or make it more readable. If I wanted to show only the totals at the territory and salesperson levels, I could select the detail row and set the Hidden property to True – but the blank rows would still appear under the groups. Or, I could delete the detail row – but then I couldn’t view the data for a sales person if I wanted to.
What I really want to do is make it so I can collapse and expand each section as needed. In SSRS, this is called toggling. I pick the row I want to change the visibility for – in this case, the detail row – and then I set a field that I will toggle on.
In the group properties, I go to the visibility section. I choose whether this row should be hidden or visible when the report is first run. To enable toggling, I check “Display can be toggled by this report item” and choose a text box – in this case, sales person.
This is a huge improvement in readability. The next step is to add toggling at the sales person level, triggered by the sales territory.
Not all properties in SSRS are true/false or a set list of options. Many can be configured dynamically, using expressions. Expressions are based on Visual Basic, and can help you do things like set dynamic background colors on cells, change grouping on the fly, or perform mathematical calculations.
Let’s take a simplistic and not-too-realistic example: I want the value at the salesperson level to have a green background if the sales are over $1,000,000. (This isn’t terribly realistic because I’m letting the user choose a date range. It would be more realistic if that was a one-year goal and the user had to pick a specific year.)
In the text box Background Color property, I choose Expression and the expression builder opens. Using an IIF statement, I say, if the total due field is greater than or equal to 1,000,000, the color should be green; otherwise, it should be white.
Now, at a glance I can tell which sales people were “high performers” – without having to look at each field and mentally calculate that.
Expressions allow you to extend the functionality of properties greatly. Become familiar with expressions. Then become great at them. You’ll thank yourself for this bit of knowledge.
Make it Work
Building an SSRS table can be a quick, simple process. Your report will look simple. I challenge you to consider how you can improve it. In each report, pick one element – the formatting of a cell, the ordering of the data, something that can be visualized – and change it. Your reports will stand out, and tell the story better. Get started with my SSRS Basics training!
Imagine this: you have a database, and it’s on a SQL Server instance, which is on a server. That server may be physical or it may be virtualized. The database, or the instance, or the server, has a high availability solution implemented. It might be failover clustering; it might be synchronous mirroring; it might be VMware HA.
One day, your queries start to run a little slower. But the performance degradation is slow, and gradual. Weeks or months slip by, and bit by bit, millisecond by millisecond, the queries are taking longer. Before you know it, queries are taking out locks, blocking each other, and leading to timeouts.
What You Want To Happen
Your server is overloaded. CPU usage is high. Available memory is low. I/O times are dragging on. You know there is another server sitting in the data center that is waiting to take on this load. It’s just sitting there, idle!
You want your HA solution to kick in and take over. Why doesn’t it realize that performance has tanked and it needs to come to the rescue?
Your SQL Server Isn’t Down, It’s Just Thinking
Chances are that if there was a disaster and your HA solution kicked in right now, you’d experience the same terrible performance on the other server, too – with the added pain of having downtime to failover.
High availability solutions are implemented to be there in case of failure. High CPU usage, low memory warnings, or excessive I/O times don’t constitute a failure. As much as you wish the workload could be transferred to the server with lower utilization, it won’t be – you have to tune your workload.
Yes, I’m telling you to roll up your sleeves and start performance tuning.
Do you have monitoring in place so you can check your baselines to see what resources are being used more than normal?
Have you checked the plan cache for the most resource-intensive queries in your server?
Are you performing regular index tuning (using a tool like sp_BlitzIndex™, so you have the right indexes for your workload?
Remember, your HA solution is there to pick up the pieces when something fails – not to be a safety net for poor performance.