You can tap into the self-education that comes from countless hours spent at home trying to locate information on the Web and turn it to your advantage in your own SQL Server-based search applications. With just a little bit of code you can help reduce your training costs and give your users an easy to use interface that will make them want to use your search applications. In this article I'll explain how to convert Google-style queries to SQL Server's full-text search CONTAINS predicate syntax
Some of the new features in Excel 2010 specific to Analysis Services
But anyway, now I’ve been given the go-ahead to blog I thought I’d list all of the new features I’ve found that are relevant to Analysis Services users. This does not include anything to do with Gemini, because Gemini isn’t part of the Technical Preview and I don’t have it yet unfortunately. I’m also not going to comment on bugs or things that don’t work in the way I’d want because, after all, this is not released software and things can and hopefully will change before RTM.
However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style. On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.
Kevin Kline : Old Performance Tuning Recommendations Die Hard
Your database knows how tables are relationally connected. DbOctopus takes advantage of that data wherever it can. Do you need to quickly see related rows from other table, without manual searching? Or to see descriptive names from FK table instead of numeric IDs? It's just a tip of iceberg of what DbOctopus can do to help you be more productive, while making everything easier.
Multipurpose Row_Number() Function
One of the features available in SQL Server 2005 is Row_Number() function. It is not only used to generate row number for each row but also used for other purposes as well. I breifly explain how it can be used for various purposes
Shudder. If you need to, here’s the way automate Excel via PowerShell. Just don’t stick this on a server someplace…
How Can I Use Windows PowerShell to Automate Microsoft Excel?
Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?
Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?
One of the custom log entries that I didn’t know about was the Execute SQL Task’s ExecuteSQLExecutingQuery and now having taken a look at it I wish I’d have known about it a lot sooner. The real power of this custom log entry is that it shows the SQL statement that gets executed against the data source which is especially valuable if the SQL statement is constructed using an expression.
In Part 4 of this series, I discussed Internet Explorer setup. To date, the series has been about steps needed to set up Kerberos Constrained Delegation with Windows Integrated Authentication. While the first parts of the series were not a step-by-step guide in how to set up Kerberos, it was meant to provide an overall checklist of things to make sure were properly set up. What happens if you have an issue with Kerberos after it is set up? That’s what this part of the series is about.
Unfortunately, many Kerberos setups have problems because:
Kerberos Constrained Delegation – Troubleshooting Tools (Part 5 of 6) - Windows Live
If you do any serious file copying on a Windows system, you'll quickly discover that there are substantial limitations to the default file copier. Ease your file copying frustrations with these five alternative copiers.
Lifehacker - Five Best Alternative File Copiers - File Management
Find your self doing lots of cut-paste special-transpose in Excel?
TRANSPOSE( and CTRL-SHIFT-ENTER will be your friend.
TRANSPOSE
Applies to: Microsoft Office Excel 2003
Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a range that has the same number of rows and columns, respectively, as an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.
When SQL is compiled, an ‘optimal’ plan is created and stored in the plan cache, the plan contains details of table and index access. To produce this plan, the optimiser uses information from various sources, including indexes, statistics and row counts.
If the optimiser would like to use certain information but can’t find it, it adds details of what it wanted to use to the plan. Inspecting the plans for these details will help us improve the performance of our SQL.
For example, if a certain index could have been useful for a given SQL statement, the optimiser looks for that index, if that index is not found, the missing index’s details are stored together with the plan.
There are various performance related items we can search for including: missing indexes, columns with no statistics, and the occurrence of table scans.
This utility makes use of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), so can be used by SQL Server 2005 or greater.
Get Performance Tips Directly From SQL Server - SQLServerCentral
No wonder Excel graphs usually look like crap. No 32 million color palette?
And who doesn’t use a color monitor with Excel these days?
Each Microsoft Excel workbook has a palette of 56 colors that you can apply to cells, fonts, gridlines, graphic objects, and fills and lines in a chart. If you are using a color monitor, you can customize the shade and intensity of the colors in the color palette for each workbook.
Color Palette and the 56 Excel ColorIndex Colors
And some add-ons to get around this limitation.
http://color-palette.qarchive.org/
This step-by-step article explains how to change the color palette so that you can use custom colors for elements of the workbooks in Microsoft Excel. You can modify colors for many workbook elements, including the following elements:
This article explains how to change the color palette so that you can use custom colors for these elements.
http://support.microsoft.com/kb/288412
There’s some “interesting” challenges keeping color consistent between Excel and PowerPoint too.
If you are a DBA monitoring more than 1 database server, Powershell is the way to go on a budget.
Problem
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. I would like to translate some of the Transact-SQL scripts that I use every day, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.Solution
One of the things that we do as DBAs is to retrieve a list of databases and their properties for auditing and reporting purposes. We check for properties such as recovery model, available free space, autoshrink, etc., and generate action items based on them. We've already seen how to access the Server object - its properties and methods - using SMO. We will dig into the object hierarchy and look at the different members of the Server object. A SQL Server instance can be described using different properties like instance name, logins, settings, all of which are members of the Server object.
Retrieve List of Databases and their Properties using PowerShell
Problem
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions.In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.
Useful script to backup all databases on a server with Idera SQL safe
alter proc dbo.sp_backupalldb
as
declare @dbname as varchar(100)
declare @fname as varchar(255)
declare @rownum as int
SET @rownum = 1
declare @dbcount as int
select @dbcount = count(*)
from sys.databases
where name not in ('tempdb')
while @rownum <= @dbcount
begin
select @dbname = name from (
select name, row_number() over (order by name) rownum
from sys.databases
where name not in ('tempdb') ) dbs
where rownum = @rownum
if @@rowcount = 0 RETURN
print @dbname
set @fname = 'D:\Backup\' + @dbname + '.safe'
print @fname
EXEC [master].[dbo].[xp_ss_backup]
@database = @dbname,
@filename = @fname,
@backuptype = 'Full'
set @rownum = @rownum + 1
end
We have all been subject to or know someone who has been in a situation where an object has been altered/created/deleted, without our knowledge, and the application comes to a screeching halt. After fixing the problem, your boss asks you some questions, like what happened, why did it happen, and who did it. SQL Server 2005 introduced a new type of trigger called a DDL trigger that can provide all the answers we need; however, you did not get a chance to implement this functionality. So... what do you do?
In SQL Server 2000 and earlier, identifying blocking issues was not an easy task. One option was to use Enterprise Manager to view existing connections to see if any blocking was occurring, or using the sp_who or sp_who2 commands. If you were really ambitious, you could write some code to extract blocking data from system tables.
In SQL Server 2005, the situation has improved. Besides Management Studio, stored procedures, and system tables, we also have DMVs and even the Performance Dashboard. However, most importantly, we have a new SQL Server Profiler event, Blocked Process Report. This event does a great job of helping you to identify blocking issues and, at the same time, provides you with much of the information you need to help correct the problem.
The following are some of the best practices adopted by SQL Server DBA's. I have covered some important stuffs starting with Tempdb, Memory, DB engine, Replication and Index.
Learn something new every day. Until now, I was using the sql client alias configuration tool to set this one up.
No more dependencies on server admins!
Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).
There's more info in MS KB Article 313295:
TCP/IP:
server=tcp:hostname
You can optionally specify a specific port number. By default, the port is 1433.
server=tcp:hostname, portNumber
Named Pipes:
server=np:hostname
You can optionally specify a specific named pipe.
server=np:\\hostname\pipe\pipeName
[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway
At times it can be necessary to move the data and or log files from one location to another on the same SQL Server. There are two ways to go about doing this task, detaching the database from the SQL Server Instance, moving the files to the new location in the operating system, and then reattaching the database to the SQL Server Instance, and using ALTER DATABASE with the MODIFY FILE option to move the files through a metadata switch, taking the database offline, moving the file in the operating system and then bringing the database back online. Both accomplish the same task, but there are a number of reasons why the ALTER DATABASE method can make more sense for doing this kind of task.
Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral
Boot from Windows 7 VHD Boot without having any native Operating System
VHD boot is a new feature of Windows 7 and can be used in very creative ways. If you have a need for Reimaging a machine (like a testing environment) back to its original configuration then booting from VHD seem to be a very easy way to do it.
To get more information on SQL Server database mirroring, check these links (Some of these were written for SQL 2005 but still apply to SQL 2008):
· Database Mirroring and Log Shipping Working Together: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Implementing Application Failover with Database Mirroring: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Database Mirroring Best Practices and Performance Considerations: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Database Mirroring FAQ: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
· Performance Boost for Database Mirroring: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx
· Troubleshooting Database Mirroring Deployment: http://msdn.microsoft.com/en-us/library/ms189127.aspx
· How to Avoid Orphaned Users With Database Mirroring:
http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!494.entry
Here is info from MySpace and the SQL Performance Team on how to delete large ordered blocks of data.
create view v1 as (select top (10000) * from t1 order by a)
and we can delete the “top” rows using simply
delete from v1
The query plan for this delete is much simpler.
and the I/O and cpu statistics demonstrate the improvement:
TextCrawler and Duplicate Cleaner are two useful tools for a DBA working with Reporting Services.
In combination with Reporting Services Scripter, TextCrawler allows you to perform comparisons of reports between servers (by changing the server name in the RDL) and to change the root path for folders in the rss scripts (provided the name is distinct).
Sometimes door #1 isn’t the best option.
Option 3: DATEADD
DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)
Returns 2009-04-09 00:00:00.000 (shock horror!)
To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:
- DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
- DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
- Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
- Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.
- Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
- Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
- Integration Services Samples – A few sample packages to start auditing and viewing lineage on.
This technology was around since the dawn of XT and could be valuable for improving SQL performance.
RamDisk and RamDisk Plus®
(a.k.a Ramdrive)RamDisk and RamDisk Plus offer dramatic improvement in the storage performance of servers and applications running on Windows Server 2003, Windows XP, and Windows 2000. Intel’s IoMeter benchmark reveals that our ramdrives typically show a 50 times performance gain over a physical hard disk. In many instances, that translates into an improvement of 3 to 10 times in overall application speed.
Good-looking SSIS Auditing, configuration, monitoring and change management tool.
Straight from the BI Blog comes social BI networks.
Here is a useful list of some of the communities and social groups for Microsoft BI, out there to get involved.
Follow the bird, the bbb-bird bird bird, bird is the word…
Twitter feeds
Facebook groups
Microsoft Business Intelligence
Linkedin groupsMicrosoft Business Intelligence
SharePoint Users Group
SQLServer CentralMicrosoft YouTube Channel
http://www.youtube.com/user/wowmsft
Microsoft BI blogs
The Microsoft BI Blog
THE BI Blogroll
Intelligent Insight on Performance Management
PerformancePoint Team Blog
Norm's PerformancePoint Server Blog
Excel Team Technical Blog
SQL Server Reporting Services Team Blog
RDA Business Intelligence Blog
Sacha Tomey's BI Blog
LATAM Business Productivity Blog
SharePoint Team Blog
SQL Data Services Team Blog
The I in BI Hayley Rixon’s BlogMicrosoft BI Websites
Microsoft People Ready Business
Partners
http://www.microsoft.com/bi/partners/default.aspx
https://partner.microsoft.com/bi
TechNet Discussion Forums:
SQL Server Analysis Services
SQL Server Data Access
SQL Server Reporting Services
Management Reporter
ProClarity - General
SharePoint - Excel Services
Data Mining
Monitoring and Analytics
SharePoint - Social Computing
SQL Server Data Warehousing
SharePoint - Collaboration
SharePoint - Business Intelligence
Office Online Discussion Forums
Volume alignment, commonly referred to as sector alignment, should be performed on the file system (NTFS) whenever a volume is created on a RAID device. Failure to do so can lead to significant performance degradation; these are most commonly the result of partition misalignment with stripe unit boundaries. This can also lead to hardware cache misalignment, resulting in inefficient utilization of the array cache. For more information on this, see Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000.
Instead of counters and while loops, this is a simple replacement.
But what if you have billions of rows in the “ErrorLog” table and you need to delete them a batch at a time so that you don’t lock up the entire table? You can use the “TOP” operator to specify the batch size and then specify an integer value that follows the “GO” keyword specifying the number of batches to execute.
For example:
DELETE
TOP(100000)FROM
ErrorLog
WHERE
ErrorTime < ‘2008-01-01′
GO 10000
Who needs Windows...
“…\devenv.exe” “path to solution file”
You can copy an paste your exising shortcut file to a new shortcut file and then modify the Target field to have the additional solution parameter.
To use both of these together, put the solution file first and the /nosplash second.
To see other command line options, run devenv.exe with the /? parameter.
Complete OLAP infrastructure without Microsoft Analysis Services, part 3
In previous part of this tutorial we've built an analytical database using Microsoft Analysis Services. We've been able to browse the database using SQL Management Studio and execute MDX queries on it.
In this part of the tutorial we will:
- build an offline static cube (*.cub file) from the relational database using C# and ADOMD.NET
- query the static cube using Microsoft Excell as static cube browser
- query the static cube with MDX queries using C# and ADOMD.NET
Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3
Project Description
Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.
All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works
Note There was a problem with the first release of Beta 2 20081228. If no add-in is visible please follow the instructions in Troubleshooting.
User Guide
Troubleshooting
Features
- Integration with SSMS (SQL Server Management Studio) 2005 and 2008
- The application is installed as a SSMS add-in
- Internals information integrated into the Object Explorer
- Transaction Log viewer integrated into the Query Results
- Allocation Map
- Displays the physical layout of tables and indexes
- Displays PFS status
- Overlay pages in the Buffer Pool
- Page Viewer
- Displays Data pages including forwarding records and sparse columns
- Displays Index pages
- Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
- Displays pages with SQL Server 2008 row and page compression
SELECT top 0 * into customer_backup
FROM customers
Be sure to create your primary keys and indexes afterwards!
If you would like a better understanding of the Data Mining Experience from the web, DM Companion looks like a good learning tool.
Of course, there’s always this tool too:
http://sqlserverdatamining.com/cloud
Sometimes transferring data quickly and safely can be a major task. In the following example I’ll show you how you can do a parameterized insert and how to use the sql transaction. This will speed up your insert tremendously.
You can download the VPC from the following location:
- VSTS “all-up” Virtual PC/Virtual Server image (6 GB download, expands to 15 GB)
- VSTS “all-up” Hyper-V image (6 GB download, expands to 15 GB)
Microsoft SQL Server 2005 Service Pack 3
Brief Description
Download Service Pack 3 for Microsoft SQL Server 2005.
Highlighting some of the pluses of SQL 2008
Following on from my previous post, in some data warehouses there is a separate dimension for time of day, so that demand through a day can be modelled. Storing time in SQL server 2005 was a bit of a cludge typically involving picking an arbitrary date (like 1/1/1900) and then tacking the time on to the end of that. Now there’s a separate time data type so it’s easy to store the right data and create the time dimension using a script like this:
declare @time time = '00:00'
declare @timekey int = 0
declare @timegrain int =15if not exists
(select * from sys.tables where name = 'dimTimeofday')
create table dimTimeofday( timekey int, TimeofDay time)
while @timekey < 1440 begin
insert into dimTimeofday(timekey,Timeofday) values (@timekey, @time)
set @time = dateadd(minute,@timegrain,@time)
set @timekey += @timegrain
endFor more on the new time data type check books on line here.
Excel inside SQL Server? It’s possible…
/*In this workbench, Robyn Page and Phil Factor decide to tackle the subject of Matrix handling and Matrix Mathematics in SQL. They maintain that 'One just needs a clear head and think in terms of set-based operations' */
Thank You for Downloading
SQL Server 2008 Upgrade Technical Reference Guide
Download details: SQL Server 2008 Upgrade Technical Reference Guide
Poor man's query governor for MDX here.
...or, what to do while you're waiting for Analysis Services to get a proper resource governor (fingers crossed for the next version).
SQL 2008 launched a few months ago... and left Reporting Services developers in the dust.
Good things will come to those that wait.
The current target date for a control that can read the 2008 RDL schema is currently the first calendar quarter of 2009. These dates can and are subject to change.
Notes from a former certification blogger.
I really recommend that you subscribe to Born to Learn.
Ask Ken why it's called Born to Learn, while you're over there.
The first stop for MCP, certification, or exam help continues to be your regional helpdesk: http://www.microsoft.com/learning/support/worldsites.mspx, as you know. The MCP newsletter is still the best, official way to get news about the program and exams: Subscription info. And don't forget the other great bloggers in Microsoft learning--if you get stuck somewhere, check in with one of these people or teams:
- Gerry: Developer Certification
- Mike: E-Learning
- Beta exams
- The Microsoft Learning web site
- Jeff: Developer Courseware
- Juliana: Microsoft Press
- ILT – Courseware Team: Instructor-Led Training
- Bill: MCT and Trainer Readiness
While I'm at it, I thought I'd answer some of your other, recent questions, too, in a little Q&A.
Where to start when dealing with certifications
Before writing this article, I posed a question to many certified individuals. The question I asked was, “What was the hardest part of your certification journey?” You would expect to hear that the hardest part was the exam(s) themselves. However, many people responded that the most difficult challenge was that they simply did not know how or where to start. They would describe how they heard of a certification, bought a book and started studying. There was no research performed, no plan established, and no evaluation afterword to debrief and learn from the experience.
Seven Steps to Certification Success : Training and Certification : Learning : Microsoft Forums
SpaceObServer - The Complete Disk Usage Management Solution
V3.3.2
SpaceObServer is a powerful and flexible hard disk space manager for Windows. It scans local and network drives using a background service and stores their structure, sizes and properties in an SQL database. In an Explorer-like user interface the collected data can be viewed and browsed in hierarchical or tabular views, 3D bar, pie, line charts and tree maps. Using the archived data you are able to track the development of the space usage from past to present, and forecast future size usage. A flexible file search, with predefined searches for very big, old or obsolete files allows filtering and listing files directly from the database. A duplicate file search is also included.
JAM Software - SpaceObServer - The Hard Disk Space Manager with Database Storage
Can you believe there are people who would charge you for a barcode font like this?
What to do if you need to roll back \ undo your virtualized client
Use the Force - To get the server and workspace versions in sync again perform a get using the force option.
OR
If you are using Hyper-V a better solution is to put your workspace on a drive that is offline to the host OS (Windows 2008) but known to the Hyper-V machine. You have to disassociate this offline drive from the Hyper-V machine before you do the rollback and re-attach afterwards. This keeps the rollback from affecting the workspace drive, leaving your workspace files the same.
Show All
Hide All