SQL Server Index Basics Given the fundamental importance of indexes in databases, it always comes as a surprise how often the proper design of indexes is neglected. It often turns out that the programmer understands detail, but not the broad picture of what indexes do. Bob Sheldon comes to the [...]
Useful SQL to check your indexes. If avg fragmentation is larger than 15% consider rebuilding the indexes: SELECT OBJECT_NAME ( object_id,database_id), * FROM [sys].dm_db_index_physical_stats, NULL, NULL, NULL, DEFAULT) AS ddips order by avg_fragmentation_in_percent desc [...]
Previously posted about finding content in a SQL Stored Procedure. Here is a nice script that creates a new Stored Procedure called SearchAllTables that will scan all tables and columns for a string input: CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT [...]
Now in public preview, Long-Term Retention (LTR) allows you to enable your databases to create weekly backups stored in your own Azure Backup vault and extend the retention period built into SQL Database from 35 days to up to 10 years. This drastically improves the functionality in Azure to serve [...]
If you are on the right track and started moving to Azure 🙂 then sooner or later you will need to move some of your databases to Azure. One option would be to spin up a VM in Azure and manage your own SQL Instance, which in my opinion bypasses [...]
We use audiences extensively and required a way to do a recon on which users are in which audiences. Obviously we can run some C# to get this but I got stuck with some arb error message and permission issues (nothing new for SharePoint). So I decided to do the [...]
Nice article on SQL Server running on a HDD vs a SSD. I am sure there are loads of more technical articles available, but nice to read something simple and to the point: The effects of an SSD on SQL Server Performance [...]
Forgive me if this is something old (like using a…well using statement in SQL). I remember a time when you had to download some obscure freeware application to scan through all your stored procedures to find references to some text or table. Years after I was faced with just this [...]
Some useful SQL queries to get web, site and list data: 1) Query to get all the top level site collections SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated FROM dbo.Webs WHERE (ParentWebId IS NULL) 2) Query to get all the child sites in a [...]
