There are many reasons why you would want to know the size of a database that you are getting ready to work with – any bit of information helps put together the picture of what you are about to walk into (or dropped knee deep into, depending on your work environment). From a DBAs perspective, knowing the different sizes of tables can assist readily in looking at the storage subsystem and making sure it is configured correctly for the database environment at hand – by no means is it a tell-all, but again, one more piece of information to add to the picture.
SQL 2005 has a few undocumented stored procedures, one of which we will make use of: sp_msforeachtable. One reason why I like this stored proc is that it pulls back the fully qualified object name within the given database; there are quite a few blogs or posts out there that use the information_schema views, which is quite fine, but these quite frequently assume that all objects in the database are using the dbo schema (even though information_schema views have the schema name in there!). I know that a lot of people still develop entire solutions using only the dbo schema (we can take this up later), but for those who are using SQL 2005/2008 to the proper extent and using schemas as part of a security model and data namespaces, then the guys at Microsoft did you a favor by insuring that the sp_msforeachtable stored procedure grabs the fully qualified name as part of the output.
The other piece it puts into practice that is quite handy is placing brackets around the schema and the table name. Why is this handy? I have seen a lot of developers use names for database objects that should be reserved names – SQL Server, just like .NET, won't stop you from doing this, but it can cause confusion, which is why SQL Server internal processes always put [] around the names, assuming that some users will make this mistake. This undocumented procedure follows that same principle – so if you end up using a reserved name for your object, have no fear, the script below still works despite your error in judgment:
declare @SpaceUsed as table
(
TableName varchar(200)
, TableRows int
, Reserved varchar(20)
, Data varchar(20)
, IndexSize varchar(20)
, Unused varchar(20)
)
insert into @SpaceUsed
exec sp_msforeachtable 'exec sp_spaceused ''?'''
select TableName
, TableRows
, ReservedInMB = cast(cast(replace(Reserved, ' KB','') as decimal(18,4))/1024 as decimal(18,4))
, DataInMB = cast(cast(replace(Data, ' KB','') as decimal(18,4))/1024 as decimal(18,4))
, IndexSizeInMB = cast(cast(replace(IndexSize, ' KB','') as decimal(18,4))/1024 as decimal(18,4))
, UnusedInMB = cast(cast(replace(Unused, ' KB','') as decimal(18,4))/1024 as decimal(18,4))
, Reserved
, Data
, IndexSize
, Unused
from @SpaceUsed
What can you do with this information? A great many things – part of the answer to that question depends on what prompted you to look at it in the first place. But we can start with one assumption: many people are looking at this information because they are concerned about the size of the objects in the database. This gives a quick purview into the system to find which objects deserve the most attention -- these results are an accumulation of all of the space used by an object across all file groups and partitions – it does not list out the space used by an object within different partitions (should you have them). If you want to get more detailed information about a particular object, break open sp_spaceused and you will see how it aggregates the information for each object across all of the logical and physical data spaces for a given database.
Feel free to e-mail with any questions/comments.
Tags: storage subsystem,
management
Categories: SQL Server
What is BI?
There are a lot of definitions for what business intelligence stands for and what scope it encompasses, in fact, the definition seems to depend on your entry point into the business intelligence arena – implementer, consumer, maintainer, etc. What pushed me to make an entry on this subject is a discussion I had with a co-worker about custom application development and UI. We were talking a lot about customer perception on how an application should work, how to look at workflows and make an app mimic that workflow when the question popped up on where the boundaries are for business intelligence – from his point of view, and the customer group we were working with, BI looked very esoteric, egg head and as one lady coined it "just some executive data".
Does BI really look that strange to people? Looking at a lot of the traditional products, I can see why this stigma has held; most of the BI products out there force a whole new interaction, or experience on the user. With the price of many of these BI products, traditionally companies have limited who uses these products to a select circle; further impressing upon the average person that BI is in some "other realm", away from the day to day tasks of the primary workforce. Some of them will claim integration points, such as with SharePoint, however, it is still loosely coupled. The failure of many BI projects to come to fruition also does not help the perception that business intelligence is, and should be, for almost anyone in a company.
It made me wonder also if some of the boundaries around what business intelligence is should change; In our conversations with the Microsoft Performance Point sales team, they had a great quote for what the new product is about: "Monetizing your data assets" (actually, they have quite a few points, but this is one that stuck with me and sticks with customers). The other big focus, which I applaud them on, is architecting the business intelligence suite to be available to all people at all points in the organization. Performance Point has been developed as something to consume, rather than a product with its own UI, its own experience, etc.
While the notion of "monetizing your data assets" works in a great many respects, for me this does not get directly to the point – while important, I feel like an action, or description of the benefit of BI is not being fully encompassed. Mostly this description does not fit for me as it seems to push the notion that everyone involved in using a BI tool or process is to be concerned with the financials of the organization they are with, but also it seems to underline that the data must have a for profit purpose, directly. Quite simply, where do clubs or non-profits, not all about monetizing their data assets. Perhaps we could broaden the definition to: "enable the access to your information assets in order to make more informed decisions" -- but here we also have to be careful of scope, because any decision? We'll be on the hook for everything at this point!
So back to how this relates to a user interface and the discussion I was having with my colleague. Custom applications are inherently designed to meet a business process need. As the organization runs through the process, there are checks and balances, phases and/or sequential steps when starting the process all through the lifecycle till an end point. One thing he and I both saw, infrequently unfortunately, is information in these applications that provided feedback on the process. How quickly did I resolve x? How many of x did I work through in a day? All of this seems to be after thought when management later decides to gather information on employee productivity or linking performance to customer feedback. Why do most developers , and shockingly, business analysts who gather the requirements, miss this as part of an application design? It's like building a version of Quicken or Microsoft Money and not having any place in the app that shows your statement balances. While this is a strategic piece of business intelligence, we all move through a process with a purpose, and if we can get better at that process (or have data to question why we have this process), then examination becomes a little easier – just a little argument for forethought into custom application development and minding what you will want to measure out of the application and the users who consume it.
Tags: business intelligence
Categories: Business Intelligence