Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Monday, March 16, 2015

Troubleshooting SQL index performance on varchar columns @ LosTechies

I was looking today into optimizing queries against a 450M row table that has an index ( not the primary key of course ) which is a string. I was checking around on the internet for some best practices and came across this interesting article:


So as a heads-up: be sure that your string type indexes ( if at all possible ) are type varchar - and that your ORM writes a query that does not require SQL Server to do a data type conversion on the field.

Apparently EntityFunctions.AsNonUnicode() enforces this:


Or - just use the ColumnType attribute on the appropriate column, which keeps things nice and clean:


Saturday, July 13, 2013

Installing SQL Server 2012 on windows server 2012: additional sources

Today I was installing SQL Server 2012 on my Windows Server 2012 instance and I ran into a bit of problems. Right out of the box, it didn't work because SQL Server couldn't locate .NET 3.5 stuff.

Installing .NET 3.5 ( 'Add roles and features' ) didn't either just by clicking. I needed to specify a location for alternative sources. I found the answer on this blogpost how to deal with the problem.


Wednesday, July 10, 2013

Intersting blog: dutchdatadude.com

Already a while ago we had a presentation at work by Jeroen ter Heerdt who is a Technology Advisor Data Platform with Microsoft in the Netherlands. He gave a cool presentation about upcoming stuff in SQL Server and BI coming from Microsoft. Mainly he talked about PowerPivot and GeoFlow.




He also blogs, find it here.

Tuesday, April 30, 2013

Understanding SQL Server statistics

I'm trying to tune a database using the excellent SQL Server Tuning advisor - which I will blog about later. However, one of its recommendations was to create statistics. Honestly, I didn't know what they were, but this post helped me out.

Turned out that SQL Server is on 'create statistics automatically', so it wasn't relevant for the performance, but it's interesting stuff.





Wednesday, April 24, 2013

Difference between NO LOCK and READPAST in SQL Server

I learned the difference today between NO LOCK and READPAST thanks to Tim Chapman. I suspect we have an SSIS package that locks the table for a significant amount of time - and I think we're going to solve the problem using READPAST. Check out Tim's post to read up on the difference between NO LOCK and READPAST.