They are elusive and hard to spot in the wild, but they exist: rare index options that can enhance the performance of your queries. If you have a solid knowledge of index basics and want to add to your collection, this is the session for you! In this 30-minute video, Jes will show you how filtered indexes, compressed indexes, and indexed views work and can improve performance.
Want to get a closer view of the demos? Try full-screening the video – it’s available in 720p and 1080p.
11 Comments. Leave new
Hi Team,
I have see this session its really good. But in the demo scripts not appearing could you please post the scripts.
Tx
Siva
Hi, Siva. You can watch the session in high resolution to see the details. Click the gear icon when you’re watching the video to see a higher resolution, and you can go fullscreen as well.
thx a lot for sharing!
Or you can watch on Apple TV or on Youtube from any console connected by hdmi like my psp3
http://michaeljswart.com/wp-content/uploads/2013/02/okapi.png
The first time I ever saw an Okapi was the weekend I attended my first SQL Saturday in Columbus Ohio. I think it was SQL Saturday 42. It’s also where I first talked to Jeremiah for the first time.
And it’s also where I talked to Brent for the first time … or would have been if he showed up (zing!)
Anyway, the family came with me that weekend and we stopped by the Columbus zoo on the way home where we all fell in love with the Okapi they had there. Ever since then we’ve been fascinated by the animal.
Thanks for a great webcast Jes! I came for the Okapi, but I stayed for the niche index features.
Thanks Michael! They are great animals. If I can’t have a giraffe as a pet someday, the okapi is my second choice!
Hello Jes,
i am watching this video right now on youtube. Thank you for sharing your knowledge.
One Question:
You created the Unique Clustered Index on both CustomerID and ProductID.
But when you queried the Indexed View by “WHERE PRODUCTID = 779” we saw a SCAN.
I would have expected a SEEK. So why is it a SCAN?
Thank you in advance, Christoph
Brent and Team,
I am the follower of your products and technical advises.
I am advised by my management team that we can separate the indexes from a Drive (in which database main file group is residing) to another Drive. Is this a good advise? Is it possible to separate Clustered indexes from the main file group? Will this give us a performance gain (like separating MDF, LDF and TempDB into each different drives)?
Thanks in advance.
Vijay
Vijay – sure, check out this video: https://www.brentozar.com/archive/2011/12/sql-server-storage-files-filegroups-video/
Hi Jes! Thanks a lot for this video, it is great!
I was researching about indexed views because we have many performance problems in our databases but it is difficult to find a solution as they are kind of mix between OLAP and OLTP 🙁
They are huge with a lot of historical data and a business team in charge of fraud investigations run queries to those tables on a daily basis. However we have many ETL process that insert new data on the tables as well on a daily basis too, some of the tables are modified each 5 minutes.
Do you have any article or opinion about this?
I know what is the better designe options for a OLAP database, and also for a OLTP… But what about when you have a mix?
Thank you very much in advance!!
Jenny – in that case, check out our Reporting in Production webcast: https://www.brentozar.com/sql/reporting-production-sql-server/