Friday, December 21, 2012

How to modify a parameter in SPFILE

Method 1:

  1. Run
    ALTER SYSTEM SET processes=(processes + 5) SCOPE=SPFILE SID='*';
  2. Reboot (Issue shutdown and startup)

if it is not a parameter that can be modified via ALTER SYSTEM then use:

Method 2:

  1. Run
    CREATE PFILE='$ORACLE_HOME/dbs/initSID.ora' FROM SPFILE='location/spfile.ora';
    this will create PFILE called initSID.ora at $ORACLE_HOME/dbs
  2. Make changes to initSID.ora file via vi editor

  3. Shutdown the database

  4. Startup (nomount) the instance where you created and altered the pfile using this pfile only

  6. Create a new spfile

  7.  CREATE SPFILE='location/spfile.ora' FROM PFILE=‘$ORACLE_HOME/dbs/initSID.ora’;
  8. Shutdown this instance again

  9. Now startup normally without PFILE or SPFILE option:

  11. Remove the PFILE (Optional)

  12. To confirm that the parameter has been modified, issue following sql statement:


[INS-30043] The grid infrastructure home does not exist or is empty

I have gotten this error while attempting to re-install Oracle on the UNIX server.

The old version of Oracle was not de-installed properly, and so when new version was being installed, I got an error "[INS-30043] The grid infrastructure home '/u10/app/oracle/product/11.2.0/grid' does not exist or is empty".sion i was installing was and the folder 11.2.0 didn't even exist.

The resolution was quite simple though - manually editing inventory.xml file. It can be found in $ORACLE_BASE/oraInventory/ContentsXML directory. I had to remove the entries pointing to the deleted folders and my issue was resolved.

inventory.xml file lists all the Oracle products installed on the machine and when directories are deleted manually, the entries are not removed from inventory.xml file. That's why it is a good idea to perform a deinstallation when removing any Oracle products from the server as opposed to just manually removing directores (applies to 11.2.0 and higher)

Wednesday, December 19, 2012

Some useful commands for vi Editor

Command Action
x delete one character
dw delete current word
dd delete current line
D delete all content to the rigght of the cursor
:u undo last command
:q quit editor without saving
:wq save and quit editor
:w write without exit
:[n] goto line [n]
b move backwards one word
i begin inserting text at the current cursor location

Tuesday, December 18, 2012

Oracle vs. SQL Server - Oracle equivalents for SQL Server DBAs

SQL Server Oracle
SELECT GETDATE() select sysdate from dual
SUBSTRING function does not take argument for starting position with negative value SUBSTR can have negative starting position in Oracle
FROM Table2
master database data dictionary
syslogins view dba_users view
sys.objects view dba_objects view
sys.tables dba_tables view
tempdb Temporary tablespace
IDENTITY field automatically incremented by 1 on INSERT Creating a sequence using:
 START WITH     1000
and a trigger to activate the sequence on BEFORE INSERT:
ISNULL(Field1, 0) returns 0 if Field1 is null
NVL(Field1, 0) returns 0 if Field1 is null

Monday, December 10, 2012

How to modify a column data type in a table that is populated with data

1) Create a table with the same name _tem (example: Table1_tmp) with the correct structure

2) Load data from Table1 into Table1_tmp using INSERT INTO ... SELECT FROM

3) Drop Table1

4) Re-create Table1, using CREATE TABLE Table1 AS (SELECT * FROM Table1_tmp)

5) Drop Table1_tmp

Wednesday, December 5, 2012


There are 3 different sets of views ALL_, DBA_, and USER_.

ALL_ views display all the information accessible to the current user, i.e. it can looks at all the shcemas the user has permissions to, DBA_ views display infor for the entire database and is intended only for admins. Then USER_ views display info from the schema of the current user.

Tuesday, December 4, 2012

Creating database users in Oracle

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege. Usually, a newly created user is granted a role similar to the predefined roll CONNECT (used in this example) that specifies the CREATE SESSION and other basic privileges required to access a database.

create user user1 identified by pwd1 DEFAULT TABLESPACE users


In order to check whether the user has been created, run

select username from dba_users;
and to check if you have the correct name for the tablespace, check dba_tablespaces table:

select tablespace_name from dba_tablespaces;

Monday, December 3, 2012


One of the most basic concepts in Oracle is a tablespace. Tablespace is sort of a logical container used to group physical objects or datafiles. Each database contains at least one tablespace.

Tablespace is a logical entity because it is not visible in the filesystem. It consists of at least one datafile, which is physically located on the filesystem.. Oracle database contains at least SYSTEM tablespace, that contains most of the information about the structure and contents of the database.

Each table or index that is stored in the database, belongs to a tablespace. The tablespace acts like a bridge between Oracle database and filesystem.

Usually Oracle database also contains TEMP tablespace, that is used for sorting data on disk in case you run out of memory assigned for sorting data when you query the database.
Also usually there is USERS tablespace for storing user objects.

Tuesday, November 13, 2012

Great news!

Just received some absolutely wonderful news! I just got an offer from my company to join the DBA team as an Oracle DBA I!
It's a cause for celebration - new and exciting opportunities, Oracle training, lots of new and exciting things and I can still stay at the company I love where I spent nearly 7 years.

Wednesday, October 31, 2012

Javascript: Class.method vs. Class.prototype.method vs. Class.apply.method

Class.method - a custom method added in this way only exists for that particular instance of an object. if you want a custom method or property to be added to all instances of the object, that's where prototype object comes in.

Class.prototype.method - prototype object can help you to quickly add a custom method to an object that is reflected on all instances of it.

Class.apply.method - allows to apply a method of one object to another.

Friday, September 28, 2012

SQL to get the names of the tables referenced in stored procedures

below is a simple sql to retrieve a list of all the stored procedures and the tables referenced in those procedures:

SELECT DISTINCT AS [Procedure Name], AS [Table Name]
FROM sysobjects as a
INNER JOIN sysdepends d ON
INNER JOIN sysobjects b ON
WHERE a.xtype = 'P'

Thursday, August 2, 2012

Application Name in a SQL Server connection string

Found an interesting feature - Application Name parameter in a connection string. It comes in very handy when you are using SQL Trace and trying to determine which application is connecting to your database, and Application Name parameter specifies what goes into Application name column in your trace file.

Sample connection string:

Data Source=myServer;Initial Catalog=myDB;User Id=myUsername;Password=myPassword;Application Name=myApp;

Wednesday, August 1, 2012

Why not ending URL with a slash resulted in 404 File not found error

Came accross an issue I have not encountered before - excluding trailing slash after URL.

The path to the web application I was accessing (sent to me in an e-mail) was http://ServerName/MainDirectory/Subdirectory. When I opened it in IE, I received 404 error - File not found.

The thing is that traditonally, when URL is pointing to a file, it does not include a trailing slash, but URL pointing to a directory does. So whenever the user does not put a trailing slash into the URL, the web server looks for a file with that name as opposed to a directory. leaving off a trailing slash results in a redirect if the file with that name is not found and goes to the folder with that name and looks for a default file in the directory. And that is why it takes slightly longer to load the URL without a trailing slash.

In my case I got an error and was quite puzzled as to why I get an error instead of redirect. I looked in IIS Manager and saw something strange - not only the application directory was configured as an application but all its subfolders. So that was why redirect didn't take place - web server did not see a subfolder as a subfolder but as an application.

I am going to look into it further and try to figure out why did developer configured each subdirectory as an application...

Tuesday, July 24, 2012

More on pivot tables - adding row totals to a pivot table

Below is a query that retrieves monthly order count for each customer and displays it in tabular form using pivot table. Second column displayed is the row total for all the months for the current year.

As an example I used Northwind database.

SELECT CompanyName, ISNULL([Jan],0) + ISNULL([Feb],0) + ISNULL([Mar],0) + ISNULL([Apr],0) + ISNULL([May],0) + ISNULL([Jun],0) + ISNULL([Jul],0) + ISNULL([Aug],0) + ISNULL([Sep],0) + ISNULL([Oct],0) + ISNULL([Nov],0) + ISNULL([Dec],0) As [Year Total],[Jan], [Feb], [Mar], [Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec] 
FROM (SELECT c.CompanyName,c.CustomerID,o.OrderID, SUBSTRING(DateName(MONTH,o.OrderDate), 0,4) As OrderMonth FROM Orders as o INNER JOIN Customers as c ON o.CustomerID=c.CustomerID WHERE YEAR(OrderDate)=Year(GETDATE())) Data
FOR OrderMonth IN([Jan], [Feb], [Mar], [Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) As MonthlyOrderQty

The result displayed will be something like this:

Company Name Year Total Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Alfreds Futterkiste 3 1 0 1 1 0 0 0 0 0 0 0 0
Ana Trujillo Emparedados y helados 1 0 0 1 0 0 0 0 0 0 0 0 0
Antonio Moreno Taquería 1 1 0 0 0 0 0 0 0 0 0 0 0
Around the Horn 4 0 1 2 1 0 0 0 0 0 0 0 0
Berglunds snabbköp 5 2 2 1 0 0 0 0 0 0 0 0 0
Blauer See Delikatessen 3 1 0 1 1 0 0 0 0 0 0 0 0
Blondesddsl père et fils 1 1 0 0 0 0 0 0 0 0 0 0 0

Tuesday, July 3, 2012

SQL Server roles

There are two types of roles when it comes to databases - server roles and database roles. Server roles should only be assigned for the advanced users, such as database administrators. Here are some server roles:

Role Description
sysadmin Can perform any task in SQL Server
serveradmin Can set server-wide configuration options, can shut down the server
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords
processadmin Can manage processes running in SQL Server
dbcreator Can create, alter, and drop databases
diskadmin Can manage disk files
bulkadmin Can execute BULK INSERT statements
public Every SQL Server user account belongs to this server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users

Database roles are security roles that exist on a database level, as opposed to the server level. There are two kinds: fixed roles and flexible roles. Fixed roles automatically exist in each database. Adding a user to a fixed role will not change user's permissions in any other database. Flexible database-level roles are simply defined as roles that you create in the database.
Here are the fixed database roles:
Role Description
db_owner can make extensive modifications to all aspects of the database. A user who is a db_owner can even drop the database. Here are some actions that can be performed by the user in db_owner role:
  • Creating users
  • Adding them to roles
  • Creating tables/views and stored procedures
  • Adding security settings for tables, views, and stored procedures
db_securityadmin Can modify role permissions and manage permissions with the exception of adding users to db_owner or other fixed database roles. that requires membership is db_owner role
db_accessadmin Users in this role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role
db_backupoperator Can create database backups but do not have permissions to restore the databases they backup. Only users in db_owner database role or in sysadmin server role or dbcreator server role can restored databases
db_ddladmin Can execute DDL commands (CREATE, ALTER, DROP) within the current database. Those users are able to create, or change, new tables, views, indexes and stored procedures. However, they cannot create users or manage security
db_datawriterCan INSERT, UPDATE, and DELETE data from any user created table
db_datareader Can read all data from all the user tables
public Every database user is a member of the Public role. If a user does not have any explicit permissions on a database object, they will inherit the permissions of the Public role. It is important to note that users cannot be removed from the Public role.

Monday, July 2, 2012


I must admit, I am guilty of using IN a lot in the WHERE clause of my queries and I am not proud of it. Replacing IN with EXISTS has significantly improved the performance of my queries and I doubt i will be going back to using IN.

SELECT * FROM Customers
WHERE CustomerID IN(SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) <10  )

SELECT * FROM Customers as c

EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing. The IN statement requires SQL Server to generate a complete result set before making a comparison.

Friday, June 22, 2012

Create failed for user 'xx' error

"Create failed for user 'xx'. User or role already exists in the current database" is likely to occur when users were not properly mapped. it is fixed by running sp_change_users_login stored procedure

USE DatabaseName

EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'

This procedure with action 'Update_One', links the specified user in the current database to an existing SQL Server login. User and login must be specified.

Thursday, June 21, 2012

Pivot tables in SQL Server 2008

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
For example, given the data from Orders table - OrderID, CustomerID, OrderDate etc., I can create a pivot table to display number of orders per each year listed in the database:
SELECT * FROM (SELECT OrderID, YEAR(OrderDate) As OrderYear FROM Orders) Data
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

will produce something like that:

2008 2009 2010
152 408 270

which would be quite difficult to accomplish without the use of pivot table.

SELECT * FROM (SELECT CustomerID, OrderID, YEAR(OrderDate) As OrderYear FROM Orders) Data
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

will produce a list of customers with number of orders for each customer per each year:
CustomerID 2008 2009 20120
ALFKI 0 3 3
ANATR 1 2 1
ANTON 1 5 1
AROUT 2 7 4
BERGS 3 10 5
BLAUS 0 4 3
BLONP 3 7 1
BOLID 1 1 1

The query below is similar to the one just above but it returns Company name instead of Customer ID

SELECT * FROM ( SELECT c.CompanyName, o.OrderID, YEAR(o.OrderDate) As OrderYear FROM Orders as o INNER JOIN Customers as c ON o.CustomerID=c.CustomerID ) Data PIVOT( COUNT(OrderID) FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

CompanyName 2008 2009 2010
Alfreds Futterkiste 0 3 3
Ana Trujillo Emparedados y helados 1 2 1
Antonio Moreno Taquería 1 5 1
Around the Horn 2 7 4
Berglunds snabbköp 3 10 5
Blauer See Delikatessen 0 4 3
Blondesddsl père et fils 3 7 1
Bólido Comidas preparadas 1 1 1

Monday, June 18, 2012

Optimizing stored procedures

1) Add SET NONCOUNT OFF - to prevent your stored procedure from returning number of rows affected, thus reducing network traffic

2) Use fully qualified object names and table names - preceeding the object names with schema name reduces the time it takes to search all schemas for the object, thus reducing the time it takes for a stored proceedure to runMake sure object names and table names

3) Avoid SQL Server searching master database for your stored proceedure by ensuring that none of the proceedure names are preceeded with "sp_", use "usp_" instead, to specify that it's a user stored procedure, not a system one.

4) If proceedure returns an integer value, use RETURN statement to return a single integer value as opposed to returning the value as a part of a recordset.

The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using the RETURN statement can boost performance because SQL Server will not create a recordset.

5) Replace all EXECUTE statements with sp_executesql. sp_executesql makes your code more reusable because it takes parameters and eliminates the possibility for sql injection.

The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute a sql batch with a parameter, and then execute the same batch with a different value for the parameter, the execution plan created the first time around will be reused for the different value of the parameter. The reuse of the existing complied plan will result in improved performance.

6) When checking for an existance of a particular record, Use IF EXISTS (SELECT 1) instead of (SELECT *) since it minimizes data processing

7) Add indexes to the fields most often used in WHERE clauses and for JOINS. Indexing the right fields can help to significantly improve the performance.

8) Utilize TRY-CATCh blocks, it is much more efficient than the old way of error checking after each sql statement.

--Your t-sql code goes here
--Your error handling code goes here

9) Utilize temp tables. For instance if the same query runs multiple times, run it only once, dumping the results into a temp table.

10) use EXISTS instead of JOINS. If the table you are joining with is not contributing any columns to your query (the select list), then you are using unnecessary IO resources on the background.

11) Avoid using DISTINCT. Usage of DISTINCT may mean you have a bad table design somewhere

12) Limit the SELECT list by returning only the columns you need, since returning too many columns can have a drastic effect on your query. Not only will it increase you chances for bookmark lookups (or key lookups), but the network and disk latency add to the query. Not to mention you will be squeezing more data into your buffer cache.

13)Use the least amount of tables to Compile Your SELECT list. An example would be let’s say you need to join on 2 tables in order to get your result set. If one of the tables contains all the fields needed for the SELECT list, but you are also able to get the same field(s) from the other table, always go with only returning the values from the one table. Doing so will limit the number of IO operations necessary to give you your result.

14)Index temp tables Temp tables are treated just like permanent tables according to SQL. They can have indexes & statistics. The only downfall is that they often cause recompiles for the statement when the result sets differ. To counter this read reducing temp table recompiles or use table variables if you have to.

15) Break down large stored procedures into several sub-procedures and call them from controlling stored procedure.

The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.

16) Try to avoid using temporary tables inside your stored procedures.

Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

17) Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

18) Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.

The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used.

19) Use SQL Server Profiler to determine which stored procedures have been recompiled too often. To c

heck if a stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

Thursday, June 14, 2012

Troubleshooting database slowness

Check task manager make sure SQL Server is not consuming more than 50% RAM available. If CPU usage is high, check sysprocesses table in master database for the number of connections that are using SQL Server - make sure you check the ones with abnormally high CPU time and spid > 50 and to exclude all system connections.

This query will show which processes have been holding the connections for more than 3 hours, as well as blocking queries:

SELECT   spid,
sqltext.text as [SQL], 
blocked As [Process Blocked],
         loginame as [User], 
         hostname as [Hostname], 
         program_name as [Application], 
         cpu as [CPU], 
         physical_io as [Physical IO], 
         memusage as [Memory Usage], 
         open_tran as [Open Transactions],
         last_batch as [Time Last Ran],
         DB_NAME(sysprocesses.dbid) As [Database Name]
FROM     sysprocesses  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE    spid > 50 
AND Datediff(hh,last_batch,Getdate()) > 3 
ORDER BY last_batch 

Next, check sys.dm_exec_query_stats for the top 10 longest running queries:

t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC

Also examine SQL Server logs to see if anything unusual has occurred.

Monday, June 11, 2012

Index defragmentation

Fragmentation can be defined as any condition that cause more than optimal amount of disk I/O to be performed in accessing a table or cause the longer disk I/O. For optimal performance of SQL queries, the data pages of tables are as contiguous as possible and pages are as fully packed as possible. Fragmentation breaks this rule thus reducing the performance of the queries. There are two levels of fragmentation: file system level (physical disk fragmentation) and index level fragmentation.

Physical disk fragmentation is the fragmentation of the database file in the file system, when file system cannot allocate contiguous space for it. As a result disk head has to move back and forth when reading from the datbase files. That happens when database file is stored on the same disk as other files, such as OS, application, or log files or when database grows frequently and in small chunks. The way to avoid it is to allocate enough space originally and to specify growth option to allocate larger chunks instead of small ones.

Before running windows defragmentation tool, SQL Server needs to be stopped, otherwise database file will be skipped.

Index fragmentation can be reduced by selecting an appropriate fill factor when creating the index.

There are two ways to defragment indexes: rebuild and reorganize.

When you reorganize an index, SQL Server physically reorders the leaf-level pages to match logical order of the leaf nodes. The process does not allocate new pages but only uses existing ones, compacting the indexes. Reorganization uses minimal resources and does not block queries or updates.

Reorganization should only be used on lightly fragmented indexes (no more than 30% fragmentation), heavily fragmented indexes should be rebuilt.

The sql below reorganizes the specified index:

ALTER INDEX index_name 
ON DBName.TableName

The important thing to keep in mind is that if you try to reorganize or rebuild an index with few pages, most likely nothing will change.

Rebuilding an index involves dropping the original index and building a new one, so that index starts clean, with minimal fragmentation. Index can be rebuilt offline and online. Rebuilding is done the same was as reorganizing except REBUILD keyword is used instead of REORGANIZE:

ALTER INDEX index_name 
ON DBName.TableName

if you want to add some options to your rebuild, use WITH clause

ALTER INDEX index_name 
ON DBName.TableName 

Friday, June 8, 2012

Analyzing index fragmentation

The sys.dm_db_index_physical_stats function is used to determine which indexes are fragmented and the extent of their fragmentation.

The function takes the following parameters: database_id, object_id, index_id, partition_number, mode.

database_id - ia an integer value that represents id number of your database
object_number - is an integer value that represents your table or view
index_id - integer that represents id of your index
mode - can be either LIMITED, SAMPLED, or DETAILED

LIMITED: Scans the smallest number of pages, which means this is the fastest mode. The LIMITED mode is equivalent to NULL and DEFAULT.
SAMPLED: Scans 1% of all pages. If an index contains fewer than 10,000 pages, then DETAILED mode is used.
DETAILED: Scans all index pages, which means this is the slowest mode, but most accurate.

You must specify all five parameters, even if their values are null.

Here are some important values returned by sys.dm_db_index_physical_stats function:

index_type_desc - index type (clustered, non-clustere)
avg_fragmentation_in_percent - percentage of the logical index that is fragmented
fragment_count - number of fragments in the leaf level
page_count - number of index or data pages.

An index always has at least one fragment (fragment_count). The maximum number of fragments that an index can have is equal to the number of pages (page_count). For example, an index that is made up of 3 pages can at the most have 3 fragments. The larger the fragment, the less disk I/O that is required. Ideally, the avg_fragmentation_in_percent value should be as close to zero as possible.

Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%

I am using the following query to retrieve the fragmentation data:

SELECT OBJECT_NAME(ps.object_id) As TableName, As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('Northwind'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 

Thursday, June 7, 2012

On index statistics

If an index is not being used much, it will have a lot of free space in the index pages, thus occupying a lot more disk space then it should and it may be worth compacting it (via rebuild or defrag) to get the disk space back. At the same time is an index is barely used, there might not be a point to spend resources to remove any kind of fragmentation.

Another thing is a non-clustered index affects performance in a number of ways, one of them being the amount of extra IO it takes to maintain each index. For example whenever a new record is added to the database, a matching record is inserted into a non-clustered index, or whenevr the record is deleted, a matching record for the index must be deleted as well, or a clustered index's value is changed during update, the matching record for non-clustered index must be updated as well.

There is a dynamic management view sys.dm_db_index_usage_stats that can help us determine whether a particular index is being used. This view does not keep historical data but keeps the data only since the last time database was open. It does not keep the data after the database has been closed.

The sql below will retrieve data for a particular database

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('DatabaseName')

Below are some useful fields from sys.dm_db_index_usage_stats view:

user_seeks - either looking up a single row or doing a range scan
user_scans - select * operation performed on the table
user_lookups - a bookmark lookup where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row
user_updates - number of times it was used in update operations (this included inserts, updates, and deletes)

Some useful queries:

--get database name based on database_id
select * from sysdatabases where dbid=7

--get object name based on object_id
FROM sys.objects
WHERE object_id = 37575172

--retrieves database_id of a current database
Select db_id()

--retrieves an index name based on object_id and index_id from sys.dm_db_index_usage_stats view
SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id

--retrieves index statistics, including table name to which the index belongs, and index name
SELECT object_name(iu.object_id, iu.database_id) as tablename, as IndexName, iu.user_seeks,iu.user_scans,iu.user_lookups,iu.user_updates,iu.last_user_seek, iu.last_user_scan,iu.last_user_lookup,iu.last_user_update 
FROM sys.dm_db_index_usage_stats as iu
LEFT JOIN sys.indexes as i ON iu.index_id=i.index_id AND iu.object_id=i.object_id
WHERE iu.database_id = DB_ID('Northwind')

The query below will return a lit of all indexes with corresponding table names that have not been used by SQL Server since the service started

SELECT  ObjectName = OBJECT_NAME(ind.object_id),  IndexName =
FROM Sys.Indexes ind            
INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1            
AND NOT EXISTS ( SELECT 1 FROM Sys.dm_db_index_usage_stats usg WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id)
ORDER BY ObjectName,IndexName

Wednesday, June 6, 2012

Query for cleaning up the history records except the last one for each item

The query below will delete all the history records for each item, except one latest record
WHERE itemHistoryId NOT IN 
(SELECT t1.itemHistoryId
 FROM Table1 as t1
WHERE t1.historyDateTime = (SELECT MAX(historyDateTime) FROM Table1 WHERE ItemID=t1.ItemID)) 
The subquery
SELECT t1.itemHistoryId
 FROM Table1 as t1
WHERE t1.historyDateTime = (SELECT MAX(historyDateTime) FROM Table1 WHERE ItemID=t1.ItemID)
will return all the records with the latest history date, one per each itemID (i.e. records to be kept), and the main query will pull all the records that are not included in this subquery and delete them

Friday, June 1, 2012

Cannot open user default database. Login failed. Login failed for user ‘UserName’.

If, when attempting to log in to SQL Server Management Studio you get an error "Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft SQL Server, Error: 4064)", click Options and in a dropdown Connect to database, type in your database name and click connect.

Saturday, May 5, 2012

How to utilize Microsoft SharePoint via Visual Studio .NET

First download appropriate installer for Visual Studio 2008 Extensions for Windows SharePoint Services 3.0 - VSeWWSv13_x64.exe

Then, download Windows SharePoint Services 3.0 - SharePoint.exe

If you attempt to install Visual Studio 2008 Extensions for Windows SharePoint Services 3.0, it will ask you to first install Windows SharePoint Services 3.0, which, in turn, will give you an installation error. There is one thing you need to do before attempting to install Windows SharePoint Services 3.0: Run SharePoint on Vista Installation Helper File. It is created for Windows Vista, but works just fine on Windows 7 and can be downloaded from here

After downloading Vista Installation Helper File, run WssVista.msi.

After install is compete, run C:\Program Files (x86)\WssOnVista\Setup\SetupLauncher.exe and when it prompts, navigate to SharePoint.exe that you have downloaded in previous steps.

After all this is done, you can safely install Visual Studio 2008 Extensions for Windows SharePoint Services 3.0 - the first file you have downloaded.

Now, in order to add SharePint dll to your project, right-click on the Visual Studio project name, select Add Reference and browse to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.SharePoint.dll" and add Microsoft.SharePoint directove to your .NET class.

Thursday, May 3, 2012

Login failed for user [name] error

So you have been moving database from one server or instance to another, you backed up and restored the database, scripted all logins, did everything in the book, and suddently, when loging in with sql server login, that worked so well before, you get "Login failed for user [name]" error.

First thing to check is whether your SQL Server is configured to use Mixed mode or Windows only authentication. because if it is configured to use Windows only, any sql login will be rejected, no matter what type of permissions it has.

Right-click on the server name, go to Properties, select Security and make sure "SQL Server and Windows Authentication mode" is selected.

Wednesday, May 2, 2012

Moving databases: Detach/Attach method vs. Backup/Restore

Personally, when moving a database from one server to another, I prefer using backup/restore since if I encounter any issues with SQL Server versions or something else, I can always restore backup somewhere else and I will also have a working copy of the database on the source server, while if anything goes wrong during the detach/attach process, I will not have a copy of the database elsewhere. My second reason is that backup/restore requires no downtime, which is always a big plus.

So far I have encountered only one case where using detach/attach scenario was better then backup/restore - I needed to move a database from the default instance to a named instance on the same server and I did not want to use backup/restore since I wanted mdf and ldf files to stay at the same location on the server. Also detach/attach is much faster than backup/restore since it only takes minutes to detach database, copy the files to another server and attach the database, and both backup and restore processes can take hours.

Monday, March 26, 2012

SQL Server 2008: Owners vs. Schemas

The other day someone asked me what is dbo, they had trouble differentiating between dbo as object owner and dbo as a schema. That conversation was what inspired this post.

Every object created in SQL Server must have an owner and most of the time the owner is dbo, which is the database owner. One can easily determine the owner of a particular database object by looking at the fully qualified name which is using the following convention:


Example: Server03.Northwind.dbo.Customers, where dbo is the owner of table Customers that belongs to Northwind database that resides on Server03.

Object gets its owner based on who created the object. By default the user account that creates the object will also own the object and only users in db_owner role can create objects owned by dbo. Someone in db_owner role can create an object owned by any user in the database. Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

A schema is a named container for database objects, which allows you to group objects into separate namespaces. It is basically a way to logically group objects such as tables, views, stored procedures etc.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
The dbo schema is the default schema for a newly created database. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

Users who are assigned the dbo schema do not inherit the permissions of the dbo user account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.

Before SQL Server 2005, schemas used to be equivalent to database users. Now each schema is a distinct namespace that exists independently of the user who created it. It can be owned by any user and its ownership is transferable.

Thursday, March 8, 2012

Show and hide paragraphs using JQuery

Below is the JQuery code I used to show and hide paragraphs when user clicks on the hyperlink - paragraph title:
<!DOCTYPE html>
<html lang="en">
 <meta charset="UTF-8" />
 <script type="text/javascript" src="js/jquery.js"></script>
 <script type="text/javascript">
   //toggle the componenet with class msg_body  
   $('a.title').click(function()  {  
    var res =; 
   $('#p' + res).slideToggle(500);


   <a href="#" class="title" id="t1">Topic 1</a>
 <p id="p1">Testing 1

<a href="#" class="title" id="t2">Topic 2</a> <p id="p2">Testing 2

<a href="#" class="title" id="t3">Topic 3</a> <p id="p3">Testing 3 </p>
<a href="#" class="title" id="t4">Topic 4</a> <p id="p4">Testing 4

</body> </html>

Tuesday, March 6, 2012

7 CSS3 properties one must know

  1. border-radius with that one you can create rounded corners
    -webkit-border-radius: 4px;   
    -moz-border-radius: 4px;   
    border-radius: 4px;  
    and even circles:
    -moz-border-radius: 50px;
    -webkit-border-radius: 50px;
     border-radius: 50px;
  2. box-shadow allows you to immediately apply depth to your elements
    -webkit-box-shadow: 1px 1px 3px #292929;
    -moz-box-shadow: 1px 1px 3px #292929;
    box-shadow: 1px 1px 3px #292929;
    -webkit-box-shadow: 1px 1px 3px green, -1px -1px blue;
    -moz-box-shadow: 1px 1px 3px green,-1px -1px blue;
    box-shadow: 1px 1px 3px green, -1px -1px blue;
    box-shadow accepts four parameters: x-offset y-offset blur color of shadow
  3. text-shadow same as box-shadow but applied to text
    .box {
    background: url(image/path.jpg) 0 0 no-repeat,
     url(image2/path.jpg) 100% 0 no-repeat;
    In the case above, first background is placed in the top left position (0 0) and second - the top right position (100% 0)
  5. background-size
    background: url(path/to/image.jpg) no-repeat;
     -moz-background-size: 100% 100%;
     -o-background-size: 100% 100%;
     -webkit-background-size: 100% 100%;
     background-size: 100% 100%;
  6. text-overflow text-overflow property can accept two values:
    • clip
    • ellipsis
    This property can be used to cut off text that exceeds its container, while still providing a bit of feedback for the user, like an ellipsis.
    .box {
       -o-text-overflow: ellipsis;
       border: 1px solid black;
       width: 400px;
       padding: 20px;
       cursor: pointer;
  7. Resize allows us to specify how a textarea is resized. Possible values: both: Resize vertically and horizontally horizontal: Limit resizing to horizontally vertical: Limit resizing to vertically none: Disable resizing
    textarea {   
       -moz-resize: vertical;   
       -webkit-resize: vertical;   
       resize: vertical;   

Tuesday, January 24, 2012

Multi-column layout

CSS3 has new properties with which one can create a multi-column layout (sort of like in a newspaper). So far it's only supported in Firefox and Safari 3 but not in IE.
Below is the code for both Safari 3 and Firefox that creates 3 columns with a 1em space between them

/*IE - not supported yet*/
column-count: 3;
column-width: 13em;
column-gap: 1em;

-moz-column-count: 3;
-moz-column-width: 13em;
-moz-column-gap: 1em;

-webkit-column-count: 3;
-webkit-column-width: 13em;
-webkit-column-gap: 1em;

Attribute selectors

•attribute beginning matches exactly
The element has an attribute called foo that begins with "bar" e.g.
•attribute ending matches exactly
The element has an attribute called foo that ends with "bar" e.g.
•attribute contains the match
The element has an attribute called foo that contains the string "bar" e.g. Examples:
a[href^="ftp:"] { ... }
a[href$=".edu"] { ... }
img[src*="photos"] { ... }

What's new in CSS3


background-origin and background-clip
multiple backgrounds

HSL colors
HSLA colors
RGBA colors

Text effects

nav-top, nav-right, nav-bottom, nav-left

attribute selectors

Basic box model
overflow-x, overflow-y

Other modules
media queries
multi-column layout
Web fonts

Monday, January 23, 2012

HTML5: fieldset and legend elements

fieldset element - is used to group a batch of controls together. Also it draws a box around the grouped elements. The title of such a group of controls is given by the first element of the fieldset - legend element. Example of grouped controls could be a question for multiple-choice test - collection of radiobuttons:
  <legend> Question 1 </legend>

</fieldset> </form>
To make sure the radiobuttons work together, they are given the same name. Fieldset element can be located ouside of a form but still be part of that form by specifying form attribute:
<fieldset form="form_id"> 
But the form attribute so far only supported in Opera browser. The HTML legend tag is used for providing a title or explanatory caption for the rest of the contents of the legend element's parent elements, in particular fieldset, figure, and details elements. Helpful links: HTML5 Forms What's different in HTML5?

Thursday, January 19, 2012

HTML5: Difference between article and section tags

An article is an independent, stand-alone piece of discrete content, sort of like a blogpost or a news item, a self-contained composition. It should be able to stand on its own as if it was independent of its surroundings and that can be independently distributable or reusable.

Section, on the other hand, is either a way of sectioning a page into different areas, or sectioning an article into sections. basically it is a thematic grouping of content, usually with a heading.

Section can be used to group related articles together. It helps to think of a web page as a newspaper that has different sections: sports, real estate, etc. with a number of articles in each section.

Each section should have a heading to identify that secion's content. If you don't use a heading in your section element, you most likely should not use the section element. In that case a div might be more appropriate.